Hi @rask ,
Your current approach is facing issues because its DAX logic and data structure are not ideal for this scenario. The main problem is trying to determine a user's default category directly from the large Products table, which is unreliable and inefficient. A much more robust solution involves creating a dedicated table specifically to manage user permissions. This change will simplify both your Row-Level Security (RLS) and the dynamic masking logic.
The first and most critical step is to create a new table, which we'll call ManagerAccess. You can create this using the "Enter Data" feature in Power BI. This table should have three columns: ManagerEmail, Category, and a crucial new column named AccessType. In the AccessType column, you'll explicitly state whether a manager's access to a given category is "Default" or "Temporary". For example, manager.a@company.com could have a row with the category "Electronics" and AccessType "Default," and another row for "Apparel" with AccessType "Temporary." Once this table is created, you must establish a relationship between ManagerAccess[Category] and Products[Category], with the ManagerAccess side being the "one" side of a one-to-many relationship.
With this new structure, your RLS rule becomes incredibly simple. You only need to create one role, perhaps named "Sales Manager," and apply a single DAX expression to the ManagerAccess table. This filter will handle all security for you.
[ManagerEmail] = USERPRINCIPALNAME()
This expression filters the ManagerAccess table to only the rows matching the logged-in user. Because of the data model relationship, this filter automatically propagates to the Products table and subsequently to your Sales table, correctly restricting the data view for both default and temporary access types.
Now, you can create the DAX measure that will dynamically mask the product names. This measure should be created in your Products table and will replace the original ProductName column in your report visuals.
MaskedProductName =
VAR currentUser = USERPRINCIPALNAME()
VAR currentProductCategory = SELECTEDVALUE(Products[Category])
-- Find the user's single "Default" category, ignoring filters from the visual.
VAR userDefaultCategory =
CALCULATE(
SELECTEDVALUE(ManagerAccess[Category]),
ManagerAccess[ManagerEmail] = currentUser,
ManagerAccess[AccessType] = "Default",
REMOVEFILTERS() -- This is crucial to get the global default setting for the user.
)
RETURN
IF(
-- Check if the product's category matches the user's default category
currentProductCategory = userDefaultCategory,
SELECTEDVALUE(Products[ProductName]), -- If yes, show the full product name.
"####" -- If no (it must be temporary access), mask it.
)
This measure works by first identifying the category of the product in the current row of your visual. Then, using CALCULATE combined with REMOVEFILTERS, it looks up the user's single "Default" category from the ManagerAccess table, completely ignoring the visual's current filters. This is the key to avoiding context errors. Finally, it compares the product's category to the user's default category. If they match, it shows the full product name; otherwise, it displays "####". To use this, you must remove the original Products[ProductName] column from your table visual and add this new [MaskedProductName] measure in its place.
This refined approach directly solves your original problems. The row duplication issue with the Region column is resolved because the measure correctly evaluates at the proper granularity without interfering with the visual's context. Furthermore, the measure is self-contained and does not depend on the original ProductName column being present in the visual, as it fetches the name itself. This method represents a standard best practice for implementing this type of dynamic security and display logic in Power BI.
Best regards,
