DAX: Rank measure needs tweaking

[mstefancik]

Hello,

I have got simple scenario.

In a company are 20 sellers. I created 2 DAX queries to rank sellers based on margin they made.

DAX:

Rank - Margin seller= RANKX(ALL(Sellers),[Margin])

Rank - Margin company = calculate(COUNT(Sellers[SellerID]),FILTER(ALL(Sellers),[Margin]>0))

Now it shows me: Seller A = 2./20 (Rank - Margin seller&"./"&Rank - Margin company)

Explanation: Seller A is on the 2nd place based on margin height from the orher 20 sellers.

DAX works well. I needed to apply RLS, so every single seller can see only his data. There is one group called Managers, they can see all the data and for them Ranking works well.

But for the rest, for Sellers group DAX is broken and it still shows 1.

Is there any workaround how to rank sellers when row level security is applied?

Thx

[CahabaData]

DAX is never broken - we just don't get the results we expect !!   so this is a guess because I haven't worked with RLS but based on your post it sounds like it is showing 1 because it applies a filter context and the recalc results within that context.

So to avoid that - I think you need to write the rank info into core data table where you have the complete sellers list using a calculated column.  That sets the data upfront rather than calcs it on the fly...  so that in your visual you can call that value without it recalculating.

Just a guess.....