Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Tricky RLS problem, probably to solve with DAX?

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 🙂

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

    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