Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TheCreepster1
Helper II
Helper II

Dynamic Data Masking

Hi,

I work in an organisation with a large number of retail outlets. We have sales data from these outlets including the name of the salesperson who made the sale. The sales data table has an employee id in it, which then joins to a employee table where dynamic data masking has been applied at source (Fabric Lakehouse). 

 

Is it possible to set up some dynamic rules in Power BI that will allow all users who work in Location A to see the unmasked data for them and their colleagues in the same location, but only to see masked data outside of their own location? We have the added complication that each shop would report into an Area Manager, and we would need the Area Manager to see all of the unmasked data for the all the Locations for which they were responsible, but only masked data for everyone else. 

 

Am I expecting to be able to do too much with this, without having to create loads of roles/groups?

3 REPLIES 3
AnalyticsWizard
Super User
Super User

Applying dynamic data masking directly within Power BI, similar to what you've described, requires a strategic approach, especially considering the complexity of different visibility levels for different users.

Here’s a simplified strategy on how you can achieve this:

 

Step 1: Set Up Your Data Model

Ensure your data model has a table that maps employees to their locations and a separate mapping for area managers to their locations. This setup will be crucial for defining security roles.

 

Step 2: Implement Row-Level Security (RLS)

You can use RLS to dynamically filter data based on the user’s login. Although RLS traditionally controls access to rows of data, you can creatively use it alongside calculated columns or measures to mask or unmask data.

 

Basic Steps in Power BI Desktop:

1. **Create Roles**: Go to the Modeling tab and click on "Manage Roles". For each location and for area managers, you’ll create a specific role. For example, “Location_A_Staff”, “Area_Manager_X”, etc.

2. **Define Filters**: For each role, you define DAX filters on your tables that determine what data the role can see. For staff at Location A, the filter would ensure they only see unmasked data for Location A. For an Area Manager responsible for Locations A, B, and C, the filter would allow them to see unmasked data for these locations.

 

Example DAX Filter for a Staff Role at Location A:
```DAX
[Location] = "Location A"
OR
USERPRINCIPALNAME() = [Employee Email] -- Assuming email can be used to identify users uniquely
```

 

Example DAX Filter for an Area Manager Role:
```DAX
[Area Manager ID] = LOOKUPVALUE([Manager ID], [User Table], USERPRINCIPALNAME(), [Employee Email])
OR
[Location] IN { "Location A", "Location B", "Location C" }
```

 

Step 3: Dynamic Masking Logic

Since RLS filters rows and not individual column values, to achieve dynamic masking, you’ll need to use calculated columns or measures that display different values based on the user’s role.

- **Calculated Column for Salesperson Name**: Create a calculated column that checks if the current user should see the unmasked name based on their location or role. If not, show a masked value.

```DAX
Salesperson Name Visible =
IF(
[Location] = USERLOCATION() -- Assume a function or logic to determine the user's location,
[Salesperson Name], -- Unmasked
"Masked" -- Masked
)
```

 

Step 4: Deploy and Test

After setting up RLS and your dynamic masking logic, publish the report to Power BI Service. Then, configure the roles for each user or group of users through the service. Test thoroughly to ensure that each user sees exactly what they’re supposed to see.

 

Considerations

- This approach requires careful setup and maintenance, especially as new locations or roles are added.
- Power BI does not directly support column-level security or dynamic masking out-of-the-box. The described method is a workaround and might need adjustments based on your specific data structure and security requirements.
- For complex scenarios, consider maintaining a separate table with masked and unmasked values, controlled by RLS, to simplify the DAX expressions.

TheCreepster1
Helper II
Helper II

Thanks @Ritaf1983 , I had come across this post before when researching the issue, but it doesnt't quite address the issue. Was hoping someone else had a closer example to what I was after.

Ritaf1983
Super User
Super User

Hi @TheCreepster1 
It seems that there is no other way to reach your goals, only with dynamic row-level security with hierarchies and groups.

For more information please refer :

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.