Microsoft Power BI Data Analyst v1.0
Question 1
You are creating a report in Power BI Desktop.
You load a data extract that includes a free text field named coll.
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?
- A. In the report, add a DAX calculated column that calculates the length of col1
- B. In the report, add a DAX function that calculates the average length of col1
- C. From Power Query Editor, add a column that calculates the length of col1
- D. From Power Query Editor, change the distribution for the Column profile to group by length for col1
Answer : A
The LEN DAX function returns the number of characters in a text string.
Note: DAX is a collection of Power BI functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.
Stated more simply, DAX helps you create new information from data already in your model.
Reference:
https://docs.microsoft.com/en-us/dax/len-function-dax
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-quickstart-learn-dax-basics
Question 2
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?
- A. Publish the reports in an app and grant the HR managers access permission.
- B. Create a new workspace, copy the datasets and reports, and add the HR managers as members of the workspace.
- C. Publish the reports to a different workspace other than the one hosting the datasets.
- D. Add the HR managers as members of the existing workspace that hosts the reports and the datasets.
Answer : A
Reference:
https://kunaltripathy.com/2021/10/06/bring-your-power-bi-to-power-apps-portal-part-ii/
Question 3
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?
- A. Viewer
- B. Admin
- C. Contributor
- D. Member
Answer : D
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
Question 4
You have a Power BI query named Sales that imports the columns shown in the following table.
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.
- A. Remove the rows in which Sales[Status] has a value of Canceled.
- B. Remove Sales[Sales_Date].
- C. Change the data type of Sale[Delivery_Time] to Integer.
- D. Split Sales[Sale_Date] into separate date and time columns.
- E. Remove Sales[Canceled Date].
Answer : AD
A: Removing uninteresting rows will increase query performance.
D: Splitting the Sales_Date column will make comparisons on the Sales date faster.
Question 5
You build a report to analyze customer transactions from a database that contains the tables shown in the following table.
You import the tables.
Which relationship should you use to link the tables?
- A. one-to-many from Transaction to Customer
- B. one-to-one between Customer and Transaction
- C. many-to-many between Customer and Transaction
- D. one-to-many from Customer to Transaction
Answer : D
One on the primary Key side (customer table), many on the foreign key side (Transaction table) of the relation.
Question 6
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?
- A. From Model view, set the Subject and Body columns to Hidden.
- B. Remove the Subject and Body columns during the import.
- C. Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent.
Answer : B
The Subject and the Body are not needed in the report. Dropping them resolves the security problem and minimizes the model.
Question 7
HOTSPOT -
You create a Power BI dataset that contains the table shown in the following exhibit.
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.
Hot Area:
Answer : 
Box 1: Cost Center -
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
Question 8
You have the Power BI model shown in the following exhibit.
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.
- A. Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function.
- B. Create a single role that filters Country[Manager_Email] by using the USEROBJECTID DAX function.
- C. For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions.
- D. Create one role for each country.
- E. For the relationship between Purchase and Purchase Detail, change the Cross filter direction to Single.
Answer : AC
A: You can take advantage of the DAX functions username() or userprincipalname() within your dataset. You can use them within expressions in Power BI
Desktop. 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
Question 9
HOTSPOT -
You have a Power BI imported dataset that contains the data model shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer : 
Box 1: Assume Referential Integrity
When connecting to a data source using DirectQuery, you can use the Assume Referential Integrity selection to enable running more efficient queries against your data source. This feature has a few requirements of the underlying data, and it is only available when using DirectQuery.
Note: The following requirements are necessary for Assume referential integrity to work properly:
Data in the From column in the relationship is never Null or blank
For each value in the From column, there is a corresponding value in the To column
Box 2: Star schema -
Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires modelers to classify their model tables as either dimension or fact.
Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a very large number of rows and continue to grow over time.
Example:
Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Question 10
HOTSPOT -
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.
Hot Area:
Answer : 
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
Question 11
DRAG DROP -
You plan to create a report that will display sales data from the last year for multiple regions.
You need to restrict access to individual rows of the data on a per region-basis by using roles.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
Answer : 
You can define roles and rules within Power BI Desktop. When you publish to Power BI, it also publishes the role definitions.
To define security roles, follow these steps.
1. Import data into your Power BI Desktop report (Step 1)
2. From the Modeling tab, select Manage Roles.
3. From the Manage roles window, select Create. (Step 2)
4. Under Roles, provide a name for the role.
5. Under Tables, select the table to which you want to apply a DAX rule.
6. 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ג€(Step 3)
7. After you've created the DAX expression, select the checkmark above the expression box to validate the expression.
8. Select Save.
Step 3: Assign Users to the role.
You can't assign users to a role within Power BI Desktop. You assign them in the Power BI service.
After you've created your roles, test the results of the roles within Power BI Desktop.
Step 4: Publish the report.
Now that you're done validating the roles in Power BI Desktop, go ahead and publish your report to the Power BI service.
Reference:
https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls
Question 12
DRAG DROP -
You create a data model in Power BI.
Report developers and users provide feedback that the data model is too complex.
The model contains the following tables.
The model has the following relationships:
✑ There is a one-to-one relationship between Sales_Region and Region_Manager.
✑ There are more records in Manager than in Region_Manager, but every record in Region_Manager has a corresponding record in Manager.
✑ There are more records in Sales_Manager than in Sales_Region, but every record in Sales_Region has a corresponding record in Sales_Manager.
You need to denormalize the model into a single table. Only managers who are associated to a sales region must be included in the reports.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Select and Place:
Answer : 
Step 1: Merge [Sales_Region] and [Sales_Manager] by using an inner join.
Inner Join: Returns the rows present in both Left and right table only if there is a match. Otherwise, it returns zero records.
Note: Sales_Region and Sales_manager
There is a one-to-one relationship between Sales_Region and Region_Manager.
There are more records in Sales_Manager than in Sales_Region, but every record in Sales_Region has a corresponding record in Sales_Manager.
Step 2: Merge [Region_Manager] and [Manager] by using inner join.
Only managers who are associated to a sales region must be included in the reports.
Note: Region_Manager and Manager.
There are more records in Manager than in Region_Manager, but every record in Region_Manager has a corresponding record in Manager.
Step 3: Merge [Sales_region] and [Region_Manager] by using a right join as new query named [Sales_region_and_Region_Manager]
Reference:
https://www.tutorialgateway.org/joins-in-power-bi/
Question 13
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in shared capacity of powerbi.com.
The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The dataset is scheduled to refresh twice a day at 08:00 and 17:00.
The report is a single page that contains 15 AppSource visuals and 10 default visuals.
Users say that the report is slow to load the visuals when they access and interact with the report.
You need to recommend a solution to improve the performance of the report.
What should you recommend?
- A. Change any DAX measures to use iterator functions.
- B. Enable visual interactions.
- C. Replace the default visuals with AppSource visuals.
- D. Split the visuals onto multiple pages.
Answer : D
One page with many visuals may also make your report loading slow. Please appropriately reduce the number of visualizations on one page.
Reference:
https://community.powerbi.com/t5/Desktop/Visuals-are-loading-extremely-slow/td-p/1565668
Question 14
HOTSPOT -
You are creating a Microsoft Power BI imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories.
You import a fact table named Sales as shown in the exhibit. (Click the Exhibit tab.)
The related dimension tables are imported into the model.
Sales contains the data shown in the following table.
You are evaluating how to optimize the model.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer : 
Box 1: Yes -
Those two columns not need in the analysis.
Box 2: No -
Can remove the surrogate key OrderDateKey from the analysis.
Box 3: No -
Tax charged not relevant for the analysis.
Question 15
You have a Microsoft Power BI data model that contains three tables named Orders, Date, and City. There is a one-to-many relationship between Date and
Orders and between City and Orders.
The model contains two row-level security (RLS) roles named Role1 and Role2. Role1 contains the following filter.
City[State Province] = "Kentucky"
Role2 contains the following filter.
Date[Calendar Year] = 2020 -
If a user is a member of both Role1 and Role2, what data will they see in a report that uses the model?
- A. The user will see data for which the State Province value is Kentucky or where the Calendar Year is 2020.
- B. The user will receive an error and will not be able to see the data in the report.
- C. The user will only see data for which the State Province value is Kentucky.
- D. The user will only see data for which the State Province value is Kentucky and the Calendar Year is 2020.
Answer : D
Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles.
Both Roles are applied, and both role filters must be met.
Incorrect:
Not B: A model relationship is limited when there's no guaranteed "one" side. You get an error message if you belong to multiple RLS roles and at least one of the roles relies on a limited relationship. But here both relationships have a guaranteed 1 side.
Reference:
https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls