Complex Row-level security

[Yggdrasill]

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

1. First, check what company the user belongs to
2. Check if the user can view special cases (1 or 0)
3. 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
4. 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.

[v-easonf-msft]

Hi, @Yggdrasill 

You can check whether the solution in this thread works for you.

http://angryanalyticsblog.azurewebsites.net/index.php/2016/12/27/dynamic-rls-via-hierarchy-in-power-... 

Best Regards,
Community Support Team _ Eason