Forum Discussion

Senthil_Kumar's avatar
Senthil_Kumar
Frequent Visitor
5 years ago

Rank changes when RLS is applied

Hi Team,

 

I have 3 tables

Accountid table (used for RLS)

Dealer Master table
Fact sales tables

 

I am showing ranking of dealers based on their sales quantity.

I also want to apply RLS in my dataset since there are user who have access to particular dealers data only, but when I apply RLS the ranking is calculated on the filterted data instead of whole set of data.

 

This is the ranking of dealer when RLS is not applied

When RLS is applied on account id : 2347, below is the result

 

But my intended result should be rank for dealer A should be 2 and for dealer F 5.

Even though dealers get filtered due to RLS, Rank should be calculated based on All dealer.

 

Note: I cannot hardcode rank using a calcualted coulmn as I have date slicer and rank will change when date slicer is used.

 

I have attached the pbix with sample data below.

Sample PBIX 

 

RANKX
RankX issue with RLS

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Senthil_Kumar 

     

    I'm not 100% sure but I think it's not possible. When you apply RLS, it means that the current user CAN'T SEE data rows that get filtered out. It also means that DAX just excludes the filtered out rows from any analysis---it treats them as if they are just not there. You can't through DAX "unhide" the rows for the duration of the calculation.

     

    Think about it this way. If you have 2 countries, the USA and the UK, and the user has only permissions to view data that belongs to the USA, then there is no way to somehow hack into data from the UK.

    • Senthil_Kumar's avatar
      Senthil_Kumar
      Frequent Visitor
      5 years ago

      Anonymous  Thanks for the reply.
      Yes, its difficult to bypass RLS.

      I was thinking of an altenative but I am stuck with implementation. Not sure if this is doable.

      I created 2 Dealer table, one name Dealer_Table_RLS which I have modeled wtih RLS table so that dealers will get filtered based on RLS and a Dealer_Table with Fact sales for calculating rank of all dealers

      Calculate Ranking for whole set of dealer using Dealer_Table and then some how lookup this ranks to the filtered Dealers from Dealer_Table_RLS  table.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Senthil,

         

        You can also compute the Ranking wihtin Power Query or Backend as this needs to be static.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello,

      I also have same scenario and couldnt achieve it and not sure if it is doable. Just trying to understand if you were able to achieve the expected result. If yes, could you please post the solution steps.

  • Senthil_Kumar's avatar
    Senthil_Kumar
    Frequent Visitor
    5 years ago

    Anonymous  Yes, I was able to acheive this but the solution might affect your report performance if your dataset size is too huge. I have created 2 copies of main view, one I used for RLS filtering purpose and another for calculating rank to which RLS is not passed on and then I used TREATAS function to fetch the rank.

    Please refer below update sample file.

    Sample PBIX 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Creating a copy of the table and not applying RLS - could this give you a problem with security?

       

      Otherwise why to apply RLS anyways - you could simple Filter.

      • Senthil_Kumar's avatar
        Senthil_Kumar
        Frequent Visitor
        5 years ago

        Anonymous I don't think it will pose any secutiry issue since we are just using the duplicated table for calculating the aggregated value.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Senthil,

       

      Thanks for the reply. I dont think i will be able to implement your approach since i have huge dataset almost 10M records and also my requirement is to create Rank based on measure value(these are again from different fact tables). I think its getting complicated.

      Looking for any simple/straight forward solution.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Anonymous 

         

        You are absolutely right. The only one way to do it so that it's dynamic is to make  a copy of the fact table and the dimensions which are being affected by RLS and then use TREATAS to move relationships from one sub-model to another. Yes, this will certainly work but your model... well, it'll at least double in size. If you can afford it, no problem. If the ranks were static, you could do with calculating them in PQ. But here it's no option 😞 So, you'll have to live with duplication of the fact table.