topic Re: Rank changes when RLS is applied in DAX Commands and Tips

Rank changes when RLS is applied

Senthil_Kumar — Mon, 03 May 2021 17:54:37 GMT

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

Re: Rank changes when RLS is applied

Anonymous — Tue, 04 May 2021 11:54:34 GMT

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.

Re: Rank changes when RLS is applied

Senthil_Kumar — Tue, 04 May 2021 15:19:18 GMT

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.

Re: Rank changes when RLS is applied

Anonymous — Thu, 22 Jul 2021 21:09:11 GMT

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.

Re: Rank changes when RLS is applied

Senthil_Kumar — Fri, 23 Jul 2021 09:48:17 GMT

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

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 12:06:33 GMT

Hi Senthil,

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

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 14:29:07 GMT

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.

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 14:56:16 GMT

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.

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 15:01:03 GMT

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?

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 15:04:44 GMT

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.

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 15:14:40 GMT

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!

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 16:41:03 GMT

Anonymous

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.

Re: Rank changes when RLS is applied

Anonymous — Fri, 23 Jul 2021 17:23:46 GMT

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

Re: Rank changes when RLS is applied

Senthil_Kumar — Mon, 26 Jul 2021 06:37:20 GMT

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