Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Senthil_Kumar
Frequent Visitor

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

Senthil_Kumar_0-1620063440345.png

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

 

Senthil_Kumar_1-1620063497497.png

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 

 

13 REPLIES 13
Senthil_Kumar
Frequent Visitor

@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
Not applicable

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.

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

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
Not applicable

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?

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
Not applicable

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

daxer_0-1627060959203.png

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
Not applicable

@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
Not applicable

@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
Not applicable

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

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.

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

Senthil_Kumar_0-1620141476654.png

 

Anonymous
Not applicable

Hi Senthil,

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.