Tricky RLS problem, probably to solve with DAX?

[marcl2]

Gents,

I have a tricky problem of RLS.

Let say that i have this kind of FACT_Sales table

I need to put in place RLS for many users.

I usually put a security table column with usernames and the BUKRS that they have the right to see.

This time I need on top to say that the users need to see things based on the RecC column too. Of course when I use a model like below and that I say one user has the right to see BUKRS A  OR RecC A (interco), it won't show the line interco B A 25 (because of the first RLS) 😞

I tend to assume that the Security tables need to be disconnected and to use some DAX to pass the conditions?

Maybe with some RLS like in this video of Reza? https://www.youtube.com/watch?v=LyeAhV2665s

Would someone be Fit enough to tackle this challenge with me or have tips to suggest? (I might have not search google enough)

Of course a really Dirty solution would be to copy the Fact_Sales and separate everything, but I'm sure it could be done elegantly.

My real fact table has millions of lines, the security tables with users will have 1000s of lines and BUKRS a,nd RecC have 100s of values. So anything really manual with 1000s of roles doesn't interest me 🙂

[v-cgao-msft · Accepted solution]

Hi @marcl2 ,

Consider adding [RecC] to the security table.

and the relationship:

then please new a rls rule:

VAR __username = USERPRINCIPALNAME ()
VAR __bukrs = CALCULATE ( MAX ( 'Security Table'[BUKRS] ), 'Security Table'[UserName] = __username )
VAR __recc = CALCULATE ( MAX ( 'Security Table'[RecC] ),'Security Table'[UserName] = __username )
VAR __result = 'Fact_Sales'[BUKRS] = __bukrs || 'Fact_Sales'[RecC] = __recc
RETURN
    __result

and test the rls:

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group