Forum Discussion

W33628's avatar
W33628
Frequent Visitor
7 years ago

RLS based on multiple columns

I have a mapping table that maps Users  to the IDs that they have access to in my report. I then join this table to my main table in Power BI and then set up a role for my Row Level Security. This works great.

 

But now I need the security based on the possiblity of that ID in multiple columns on each row. For example if any of the 3 ID fields is 567, the person with access to that ID can see all of those rows. So basically it needs to be if ID=567 or ID2=567 or ID3=567. However, in Power BI I can only join my mapping table on 1 column. Is there a way to do this?

 

Example of Mapping Table

User                        ID

[email protected]567
[email protected]789

 

Example of Data Table

Sales     ID     ID2   ID3

50 567789
100567  
1000789567 

5 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage

    Hi W33628 ,

    In your scenario, we cna use lookupvalue function to get ID from the first table, then use or function to filter the data.

    Best Regards,

    Teige

    • W33628's avatar
      W33628
      Frequent Visitor

      TeigeGao 

       

      Where would I put that function? Would it be built into the Role I have defined? Can you provide an example?

      • ncbshiva's avatar
        ncbshiva
        Advocate V

        Hi All,

         

        I also need solution for this. Can anyone help us ?

         

        Regards,

        Shiva

    • SathyaG35's avatar
      SathyaG35
      Frequent Visitor

      Hi, 

       

      I noticed you posted this answer can you please post any example code on how to handle it?