Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A company is implementing row-level security (RLS) in a Microsoft Fabric semantic model. The requirement is that sales managers should only see sales data for their assigned region. The 'Sales' table contains a 'RegionID' column, and there's a 'Users' table with 'UserID' and 'RegionID' columns. You have already created a role named 'SalesManager' in the semantic model. Which DAX filter expression should you apply to the 'Sales' table within the 'SalesManager' role to enforce this RLS rule?
- ACALCULATE(VALUES('Sales'[RegionID]), RELATEDTABLE('Users')) = 'Users'[RegionID]
- BFILTER('Sales', 'Sales'[RegionID] IN SELECTCOLUMNS(FILTER('Users', 'Users'[UserID] = USERNAME()), 'Users'[RegionID]))
- C'Sales'[RegionID] = USERNAME()
- DLOOKUPVALUE('Users'[RegionID], 'Users'[UserID], USERNAME()) = 'Sales'[RegionID]
Show answer & explanationAnswer & explanation
Correct answer: D. LOOKUPVALUE('Users'[RegionID], 'Users'[UserID], USERNAME()) = 'Sales'[RegionID]
The LOOKUPVALUE function is used to retrieve the RegionID for the current user (USERNAME()) from the Users table, and then this value is compared to the RegionID in the Sales table, effectively filtering sales data by the user's assigned region.
Why the other options are wrong
- A. This expression is syntactically incorrect and uses functions inappropriately for this RLS scenario.
- B. While this approach could technically work, it is overly complex and less efficient than using LOOKUPVALUE for this specific scenario.
- C. This assumes the USERNAME() directly corresponds to a RegionID, which is incorrect as USERNAME() typically returns the user's login.
DAX for RLS (Context)
Data Analysis Expressions (DAX) used within security roles in a semantic model to dynamically filter data based on the authenticated user's identity or attributes.
- Evaluated in the context of the current user.
- Commonly uses functions like USERNAME(), USERPRINCIPALNAME(), and LOOKUPVALUE.
- Filters data at the row level, ensuring users only see authorized data.
Memory trick: Lookup the user's identity to filter their view.