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
