Rank based on dynamic slicer selection

[testyuiiop]

Hi All,

I have a sales table, and I am ranking my customers based on below dax:

    RANKX(

        ALLSELECTED(Sales[Cust ID],Sales[Cust Name])

        ,[Total Sales]

        ,

        ,ASC

    )

I also have two slicers on my report , one which will filter customer location and another year. I have only one sales table and all columns are coming from it. When I don't select anything it give the correct ranking but when I apply filter on the slicers it doesn't work. How I can make the rank function work based on filter selection?

[v-xuxinyi-msft · Accepted solution]

Hi @testyuiiop 

@lbendlin , thanks for your concern about this case.

I’d like to acknowledge the valuable input provided by the @Sahir_Maharaj. His initial idea was instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.
In my investigation, I took the following steps:

My sample:

I created two new tables for each of the two slicers.

There is no relationship between the three tables.

I also created a following formula just to get the measure.

Total Sales = CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(Sales, Sales[Cust Name]))

Create a measure as follows

rank = RANKX(ALLSELECTED(Sales), [Total Sales], , ASC, Dense)

Result:

@testyuiiop , if this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it!

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[testyuiiop]

@lbendlin 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[testyuiiop]

no this doesn't work

[Sahir_Maharaj]

Hello @testyuiiop,

Can you please try this:

Customer Rank = RANKX(
    ALLSELECTED(Sales), 
    [Total Sales], 
    , 
    ASC, 
    DENSE
)