I'm struggling with finding the correct solution to a problem that I have.
I have a user table with email addresses.
| email | company id | department id | division id | special case |
| a@b.com | A | 0 | 0 | 0 |
| b@c.com | B | 40 | 0 | 1 |
| c@c.com | B | 0 | 0 | 0 |
Now I also have a fact table like so
| fact id | company id | department id | division id | special case |
| 234567 | A | 99 | 0 | 0 |
| 234568 | A | 99 | 0 | 1 |
| 234569 | B | 40 | 0 | 0 |
| 234570 | B | 0 | 0 | 0 |
The steps I need to consider are these
- First, check what company the user belongs to
- Check if the user can view special cases (1 or 0)
- If user has 0 in division id then this user will see all divisions and all department, if not use the value to filter fact table
- if user has 0 in department id then this user will see all departments under respective divisions
How do I Combine this logic into a DAX code for the roles?
I tried implementing similar approach to what Reza Rad does at Radacad: https://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi
I also tried to combine the company id with the special case value, e.g. A-0 (company A and no special cases) or B-1 (company B with special cases) and use that as a Row ID to filter the rows in the fact table. However I don't know how to insert the other logic in 3 & 4.
but with no real luck.
