T2-One-to-25

You are creating a report in Power BI Desktop. You load a data extract that includes a free text field named col1. You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of the model. What should you do?

• In the report, add a DAX calculated column that calculates the length of col1

• In the report, add a DAX function that calculates the average length of col1

• From Power Query Editor, add a column that calculates the length of col1

• From Power Query Editor, change the distribution for the Column profile to group by length for col1

Answer explanation

1. Power Query Editor -> View -> Enable Column Profile

2. Select three dots (top left corner) in the profile pane appear at the bottom of the Query Editor window.

3. Group By -> Text length

You have a collection of reports for the HR department of your company. The datasets use row-level security (RLS). The company has multiple sales regions. Each sales region has an HR manager.You need to ensure that the HR managers can interact with the data from their region only. The HR managers must be prevented from changing the layout of the reports. How should you provision access to the reports for the HR managers?

• Publish the reports in an app and grant the HR managers access permission.

• Create a new workspace, copy the datasets and reports, and add the HR managers as members of the workspace.

• Publish the reports to a different workspace other than the one hosting the datasets.

• Add the HR managers as members of the existing workspace that hosts the reports and the datasets.

Answer explanation

Reference:https://kunaltripathy.com/2021/10/06/bring-your-power-bi-to-power-apps-portal-part-ii/

You need to provide a user with the ability to add members to a workspace. The solution must use the principle of least privilege. Which role should you assign to the user?

• Viewer

• Admin

• Contributor

• Member

Answer explanation

Member role allows adding members or other with lower permissions to the workspace.Reference:https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-roles-new-workspaces

You have a Power BI query named Sales that imports the columns shown in the following table in the image.Users only use the date part of the Sales_Date field. Only rows with a Status of Finished are used in analysis. You need to reduce the load times of the query without affecting the analysis. Which two actions achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

• Remove the rows in which Sales[Status] has a value of Canceled.

• Remove Sales[Sales_Date].

• Change the data type of Sale[Delivery_Time] to Integer.

• Split Sales[Sale_Date] into separate date and time columns.

• Remove Sales[Canceled Date].

Answer explanation

Removing uninteresting rows will increase query performance.

Splitting the Sales_Date column will make comparisons on the Sales date faster.

You build a report to analyze customer transactions from a database that contains the tables shown in the following table in the image.

You import the tables.Which relationship should you use to link the tables?

• one-to-many from Transaction to Customer

• one-to-one between Customer and Transaction

• many-to-many between Customer and Transaction

• one-to-many from Customer to Transaction

Answer explanation

One on the primary Key side (customer table), many on the foreign key side (Transaction table) of the relation.

You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent during the past year. More than 10 million records are returned. You build a report analyzing the internal networks of employees based on whom they send emails to. You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size. What should you do?

• From Model view, set the Subject and Body columns to Hidden.

• Remove the Subject and Body columns during the import.

• Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent.

• None of the options

Answer explanation

The Subject and the Body are not needed in the report. Dropping them resolves the security problem and minimizes the model.

You create a Power BI dataset that contains the table shown in the following exhibit image.

You need to make the table available as an organizational data type in Microsoft Excel.How should you configure the properties of the table? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.

• Row label: Cost Center

  Key Column: ID

  Is featured table: Yes

• Row label: Cost Center

  Key Column: ID

  Is featured table: No

• Row label: Name

  Key Column: ID

  Is featured table: No

• Row label: Name

  Key Column: ID

  Is featured table: Yes

Answer explanation

Box 1: Name -The Row label field value is used in Excel so users can easily identify the row. It appears as the cell value for a linked cell, in the Data Selector pane, and in the Information card.Box 2: ID -The Key column field value provides the unique ID for the row. This value enables Excel to link a cell to a specific row in the table.Box 3: Yes -In the Data Types Gallery in Excel, your users can find data from featured tables in your Power BI datasets.Reference:https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-excel-featured-tables

You have the Power BI model shown in the following exhibit image.

A manager can represent only a single country. You need to use row-level security (RLS) to meet the following requirements:

✑ The managers must only see the data of their respective country.

✑ The number of RLS roles must be minimized.

Which two actions should you perform? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

• Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function.

• Create a single role that filters Country[Manager_Email] by using the USEROBJECTID DAX function.

• For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions.

• Create one role for each country.

• For the relationship between Purchase and Purchase Detail, change the Cross filter direction to Single.

Answer explanation

A: You can take advantage of the DAX functions username() or userprincipalname() within your dataset. You can use them within expressions in Power BIDesktop. When you publish your model, it will be used within the Power BI service.Note: To define security roles, follow these steps.Import data into your Power BI Desktop report, or configure a DirectQuery connection.1. From the Modeling tab, select Manage Roles.2. From the Manage roles window, select Create.3. Under Roles, provide a name for the role.4. Under Tables, select the table to which you want to apply a DAX rule.5. In the Table filter DAX expression box, enter the DAX expressions. This expression returns a value of true or false. For example: [Entity ID] = ג€Valueג€.6. After you've created the DAX expression, select the checkmark above the expression box to validate the expression.Note: You can use username() within this expression.7. Select Save.C: By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filtering with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. Select this option when you've also implemented dynamic row-level security at the server level, where row-level security is based on username or login ID.Reference:https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

You have a Power BI imported dataset that contains the data model shown in the following exhibit.

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

• Changing the [Cross Filter direction] setting of the relationships will improve report query performance

  The data model is organized into a [star schema]

• Changing the [Assume referential integrity] setting of the relationships will improve report query performance

  The data model is organized into a [star schema]

• Changing the [Assumre referential integrity] setting of the relationships will improve report query performance

  The data model is organized into a [snow flake schema]

• Changing the [Cardinality] setting of the relationships will improve report query performance

  The data model is organized into a [snow flake schema]

You have a Power BI model that contains a table named Sales and a related date table. Sales contains a measure named Total Sales.You need to create a measure that calculates the total sales from the equivalent month of the previous year.How should you complete the calculation? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.

• CALCULATE

  [Total Sales],

  (SAMEPERIODLASTYEAR 'Date' [Month]

  )

  )

• CALCULATE

  [Total Sales],

  (PARALLELPERIOD 'Date' [Month]

  )

  )

• SUM

  [Total Sales],

  (PARALLELPERIOD 'Date' [Month]

  )

  )

• EVALUATE

  [Total Sales],

  (PARALLELPERIOD 'Date' [Month]

  )

  )

Answer explanation

Box 1: CALCULATE -Box 2: PARALLELPERIOD -PARALLELPERIOD returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.Syntax: PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>) dates: A column that contains dates. interval: The interval by which to shift the dates. The value for interval can be one of the following: year, quarter, month.Incorrect:SAMEPERIODLASTYEAR returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.Syntax: SAMEPERIODLASTYEAR(<dates>)DATESMTD returns a table that contains a column of the dates for the month to date, in the current context.Syntax: DATESMTD(<dates>)Box 3: 'DATE' [Month]Reference:https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Example Syntax from Microsoft:

= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)