Rank changes when RLS is applied

[Senthil_Kumar]

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 

[Senthil_Kumar]

@Pravallika05  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]

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]

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

[Pravallika05]

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]

OK, I've just had a thought. Since RLS conditions are after all a piece of DAX, that is logical expressions, you could craft an expression that will unhide all rows in very specific circumstances. For instance, you could write some logic that says to show all the rows if, say, some field in some table has been selected. That would then, I think, enable you to get the ranks without duplicating the fact table and dimensions. Think about it... The table that governs this behaviour could be hidden and you could place filters on the table using a measure(!!!), thus enabling the visibility of all the rows only for the calculation. How about this?

[Pravallika05]

Thanks for suggestion.

RLS (Filter data for that particular vendor) will be applied first on entire dataset and then Rank logic will get executed. Just giving a thought that creating any expression/logic to unhide rows would work ?

I am doing research on it. This Rank is always been complicated in PowerBi with the way of requirement i have.

If anyone finds solution please suggest and will greatly appreciate your help!

[Anonymous]

OK, if you go to the documentation, you'll find this:

Hence, your only option is to replicate a table or a set of tables. No other way, I'm afraid 😞

Here's the documentation: Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Docs

[Anonymous]

@Pravallika05 

Again, as much as I know filtering is based on a logical DAX expression. Therefore you can make it dependent on anything you want in the model. Hence, you can put a filter on a hidden auxiliary table via code in a measure and then the measure will see everything in the model. THat's enough to calculate what you want.

[Anonymous]

@Pravallika05 

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.

[Anonymous]

@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.

[Pravallika05]

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]

@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]

Hi Senthil,

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