Forum Discussion

mstefancik's avatar
mstefancik
Icon for Advocate IV rankAdvocate IV
10 years ago

DAX: Rank measure needs tweaking

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

1 Reply

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    10 years ago

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