Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 4
This question is part of repeated scenario questions that contain a similar stem but with a different solution for each question. You need to identify if the given solution solves a particular problem. Each set of repeated scenario questions might contain either none, one, or many solutions.
To the existing Power BI model, you import one more table DimEmployee to analyze the yearly income of your customers across different occupations and sales territories.
You have to grant report access so each employee can access the customer’s income and occupation data only for the Sales Territory Region he is assigned.
Solution: You create a row-level security role with the filter:
DimEmployee [Email Address] = USERNAME ()
You assign this role to all your employees.
Does the solution meet the goal?
-
A
Yes
-
B
No
Reveal correct answer
Correct answer: A
Explanation
The given solution meets the goal. Option Yes is the correct answer. Continue reading for the detailed discussion.
You can use either of the DAX functions USERNAME () or USERPRINCIPALNAME () to dynamically filter rows based on the user’s login (email address).
Reference Link: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls#using-the-username-or-userprincipalname-dax-function
When an employee logs into the Power BI service, based on the user login (email address), first, the DimEmployee table is filtered. Since there is a bidirectional relationship between DimEmployee and DimSalesTerritory table, DimEmployee filters DimSalesTerritory for only the sales territory regions the employee is assigned (Note the SalesTerritoryKey column in the DimEmployee table).
Note: A bidirectional filter is required to propagate the filter from DimEmployee to DimSalesTerritory as, by default, a single directional filter propagates the filter from the 'one' side to the ‘many’ side (and not vice-versa) of a relationship.
Once the DimSalesTerritory table is filtered, filters are transferred to DimGeography and DimCustomer. The below image will help you visualize how the filters propagate between the tables (filled with sample data).
An illustration is for the employee Tom.
Note: By default, row-level security filter uses single-directional filters irrespective of whether the relationship is single directional or bi-directional. To propagate the security filters (RLS filters) from DimEmployee to DimSalesTerritory, enable the option Apply Security filter in both directions.
Reference Link: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls#define-roles-and-rules-in-power-bi-desktop (below point 8)
Quick Preview:
Per the discussion, when the employee Tom logs in, he can view the aggregate income data only for the Sales Territory Region Australia.
Option Yes is the correct answer.
PBIX File Link: RLS UPN
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
