Row level security without relationship?

[Anonymous]

So I'm trying to implement dynamic RLS for days now, I'm loosing my mind 🙂

. I've read all the Radcad articles about it, but I keep getting stuck at one point. How do you implement a user table defining the level of security when you don't have a single column (but multiple) on which you can define a relationship in the fact table?

I have a table dertermining the security roles, where a single emailadres can have access to several CC-Names (see Ian@example.nl) and a single CC-Name can have multiple emailadresses (see Consulting CI)

CC-Name	ID	ParentID	Email
Consulting CI	1	2	Ian@example.nl
Advisory Consulting	2	3	Peter@example.nl
Advisory	3	4	Chris@example.nl
Advisory Total	4	5	Sara@example.nl
TOP	5		Zoe@example.nl
Consulting PS	6	2	Ian@example.nl
Consulting CI	1	2	andreas@example.nl

The fact table consists of a row for every employee depicting the total cost center structure they belong to: 

EmployeeID	CCL1	CCL2	CCL3	CCL4	CCL5	Score
1	Consulting CI	Advisory Consulting	Advisory	Advisory Total	TOP	5
2	Consulting CI	Advisory Consulting	Advisory	Advisory Total	TOP	5
3	Consulting CI	Advisory Consulting	Advisory	Advisory Total	TOP	5
4	Consulting CI	Advisory Consulting	Advisory	Advisory Total	TOP	52
5	Consulting CI	Advisory Consulting	Advisory	Advisory Total	TOP	14
6	Consulting CI	Advisory Consulting	Advisory	Advisory Total	TOP	17
7	Consulting PS	Advisory Consulting	Advisory	Advisory Total	TOP	15
8	Consulting PS	Advisory Consulting	Advisory	Advisory Total	TOP	19

My question: how can I link those two tables together in order to create dynamic RLS? Is that really neccessary, or can I do something fancy with disconnected tables?

Thanks so much!

Best, Strike88

[ibarrau]

Hi. RLS can be tricky, but you can do that. I'm spoiling my blog with this post because I was going to write about this haha.

You can try adding a rule in your fact table RLS like this:

[EmployeeDCCL1] IN SUMMARIZE(FILTER(First_table, First_Table[Email] = USERNAME()), Fist_table[CC-Name])

I should show the cc-names of that email and check with the "IN" if they are in the fact.

In addition to this, let me warn you that RLS should be applied to dimension. Applying rls to facts can result in low performance.

Hope this works.

[Anonymous]

Hi @ibarrau ,

Thanks for trying to help me, I'm really enjoying this communicty!

Unfortunately, I did not solve my issue with your solution. See screenshot below. What did I do wrong?

Do I need to create a relationship between the tables?

Thanks in advance!

[amitchandak]

@Anonymous , I saw this long back. But I remember they shown come calculation around RLS. Check if this can help

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613

[Anonymous]

Hi @amitchandak , 

Thanks for taking the time to get back to me. This doesn't solve my issue unfortunately, or I'm missing something.. I'm in need of help to implement this. How would that work for my particular situation?

Best regards,