Rank DAX Function

[kdesai]

Hi I want to list the top 10 number of unique  customers  visited a representative with the customer representative's location.

Without adding the location to the visual the rank works fine!

But After I add the location, I think the rank function includes the location also in the calculation of Rank.

How to avoid the Rank function from considering the location?

My DAX is as below-

Rank_Cus_Representative = RANKX(ALL(Table1[Rep]),CALCULATE(DISTINCTCOUNT(Table1 [Customer_Id])))

Raw data is as shown below

Let me know if there is a better way to do this!

[v-cherch-msft · Accepted solution]

Hi @kdesai

You may try to use ALL function to avoid the Rank function from considering the location as below. If it is not your case, please share your data sample. Here are the references for you:

https://community.powerbi.com/t5/Desktop/RANKX-of-a-measure/td-p/231664

https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/

Rank_Cus_Representative =
RANKX (
    ALL ( Table1[Rep] ),
    CALCULATE (
        DISTINCTCOUNT ( Table1[Customer_Id] ),
        ALL ( Table1[PCP_Location] )
    )
)

Regards,

Cherie