Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi community,
I have implemented quite a complex DRLS case, where I use the Userprinciplename() to filter the data based on a permissions table called Dashboard_Permissions. For your understanding, here is the situation:
I created a role whereby I filter the Users table (holds all users) using the Userprinciplename() of the current user using Power BI, which filters through to the other tables like this:
Users 1 - * Requests
Requests 1 - * Dashboard_Permissions
Dashboard_Permissions * - 1 Accounting_Divisions_Locations
Accounting_Divisions_Locations 1 - * Flexera_Evidence_Assets
As you can see, I want to use the unique locations from Accounting_Divisions_Locations to eventually filter my data (Flexera_Evidence_Assets table) using filter propagation on the Location field. The Accounting_Divisions_Locations table is a dimensional table that contains unique Locations.
I can of course use a bi-directional active filter with security filter (between Dashboard_Permissions and Accounting_Divisions_Locations), however I have come to know this is not the most performance-friendly solution, so I need to use DAX.
A person usually has a specific Corporate_Unit and Location combination.
What complicates matters is that I have a situation whereby a manager can have access to a certain Corporate Unit and should also have access to all locations under this Corporate Unit.
The structure of Dashboard_Permissions is as follows:
Permission_ID | Request_ID | Corporate_Unit_ID | Location_ID | Approved_date | Active |
1 | 1 | 1011 | NULL | 2021-03-30 16:06:33.4000000 | 1 |
2 | 2 | 1002 | 1002 | 2021-03-30 16:10:06.1833333 | 1 |
In the case that Location_ID is NULL, this means that this person(request_id in this case) should have access to all locations under this corporate unit (in this case that is BeCSE/GR), otherwise it should just take the location_id and lookup the value in the Accounting_Divisions_Locations table. I would also have to keep in mind the Active flag (1/0) which indicates if the permission is active or not.
An illustration what I want in SQL (using joins and coalesce):
Permission_ID | Request_ID | Corporate_Unit_ID | Location_ID | Approved_date | Active | Location |
1 | 1 | 1011 | NULL | 2021-03-30 16:06:33.4000000 | 1 | BeCSE/GR/Corporate |
1 | 1 | 1011 | NULL | 2021-03-30 16:06:33.4000000 | 1 | BeCSE/GR/Datacenter |
1 | 1 | 1011 | NULL | 2021-03-30 16:06:33.4000000 | 1 | BeCSE/GR/Store |
2 | 2 | 1002 | 1002 | 2021-03-30 16:10:06.1833333 | 1 | BeCSE/BE/Datacenter |
How can I implement this in DAX when I have two relationships between the Dashboard_Permissions and Accounting_Divisions_Locations table? How do I make sure that the filter propagates through? I want to specify this in the manage roles section.
Thank you for your help,
Jovan
"I can of course use a bi-directional active filter with security filter (between Dashboard_Permissions and Accounting_Divisions_Locations), however I have come to know this is not the most performance-friendly solution, so I need to use DAX."
Make sure you test that assumption on your actual data. It might not be an issue for your scenario, and it would save you a lot of headache if you can let the data model do the work.
Please explain the business scenario for Dashboard_Permissions * - 1 Accounting_Divisions_Locations again.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |