Greeting fellow Power BI enthusiastics!
I have a really hard RLS solution, yet to be completed, which I need professional help with :-)
Lets get right into it,
In my model I have the following connected tables:
Red marks = Discussed in text
Greedn stars = Keys used to create the realtions between tables
How it works now:
The RLS in set as User.[Email] = username(). When a matching user of an emailadress is watching a report it will filter out the matching Customer.[CustomerName], after that the report is filtered by a matching row in the BookingCube.[Level 1] column.
Which means, only Customers that belong to Level 1 can be shown. Level 1 is the top hierarchy of the organisation.
Simplified: Users belong to a Customer. Customers belong to one level.
Note: Level 1, Level 2 and Level 3 all have matching Customer and Users, it would work to set the relation towards any of the Levels. But I am only able to show Level 1 with this solution.
What I need:
A way to check if the Customer belongs to Level 1, Level 2 or Level 3. Where the match is made, that is what we display!
To make this abit harder, notice the Customer[ParentID] marked in red. IF a Customer have a ParentID it means that it is connected to a Customer of a higher level. Which means the Customer with a ParentID is either a member of Level 2 or 3. If the Customer.[ParentID] is NULL. It means it is Level 1.
A Customer on Level 1 should also be able to see his/her Level AND all underlying levels. ( 1 > 2 > 3). Not the other way around.
If something is unclear, just ask and I will develop.
Any type of help would be much appreciated. I´ve been looking though various types of Power Query and DAX solutions, without being able to move forward.
Based on your description, you want to apply row level security on User level to get the unique related Customer. Then filter the Booking Cube based on this Customer's Level. Right?
If you build the relationship between BookingCube and Customer table, it will only filter the corresponding Customer with level you selected. We can't apply IF() condition to check the Customer level because the row level filter expression will not determine current row context from other table, even one User has only one related Customer. If you delete the relationship between BookingCube and Customer in order to filter the all Level data, the filtered table context will not be passed to BookingCube.
In this kind of multiple table scenario, the filtered table context for row level filter can only be determined with existing relationship. We can't break the existing relationship to delegate filter context manually for custom logic. For your requirement, it can't be achieved.