Ranking value changed when filtering only one value

[Bourne_Xian]

Hi, I'm asking anything will cause the rank value changed when filtering only one value?
Thank you for your help!

With DAX:

Sample_Rank = RANKX(ALL(table[category]),[measure],,DESC,DENSE)

E.g. original ranking for category A is 5, once filter slicer selected one category A, then the ranking changed to 6.

If select more than two category, A & B, then ranking show correct value 5.

Also check the following posts which with same issue, no answer provided:
https://community.fabric.microsoft.com/t5/Desktop/Ranking-problem-when-filtering-only-one-value/td-p...

RANKX value changes for filtered value when it sho... - Microsoft Fabric Community
Ranking changes when applying filters - Microsoft Fabric Community

[techies]

Hi @Bourne_Xian please check this , 

Category Rank =

IF(

    HASONEVALUE(productcategory[Categories]),

    CALCULATE(

        RANKX(

            ALL(productcategory[Categories]),

            [sales amount vale],

            ,

            DESC

        ),

        VALUES(productcategory[Categories]) 

    ),

    BLANK()  

)

[Bourne_Xian]

Thanks, getting same issue after applied.

[Anonymous · Accepted solution]

Hi All,
Firstly  ryan_mayu and  techies  thank you for yours solutions!
And @Bourne_Xian ,According to your question, after a long time verification, we found that it is because of the date filter's effect on the category A that it can't display the correct value properly, so based on this, we just need to create a new calculation table to make sure that our category A won't receive the effect of the date filter, then we can display the value we want to need.

Measure = 
 VAR rankedCategories =
    SUMMARIZE (
        ALLSELECTED ( data[Category A] ),
        [Category A],
        "Test", [Rank Category A by Measure 1]
    )
RETURN
    SUMX ( FILTER ( rankedCategories, [Category A] IN VALUES ( 'Table'[Value] ) ), [Test] )

Rank Category A by Measure 1 = 
IF(
    HASONEVALUE(data[Category A]),
    RANKX(
        ALLSELECTED(data[Category A]),
        calculate(SUM(data[ Measure 1 ]))
    )
)

Table = SUMMARIZE(data,[Category A])

If you have further questions you can check out my pbix file, I hope this helps and I would be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Bourne_Xian]

Hi @Anonymous ,
Thanks for the investigation & provide root cause to us.

Seems this solution only works for one slicer, and without table relationship.
When I bring it into real project, I have multiple slicers on UI that may apply at the same time, slicer fields may in different tables with relationship. The use of filter propagation break this solution.

We may consider create a support ticket. Again, thanks for your help.

[Bourne_Xian]

Thank you for your kindly help. Provided solution looks good 👍

[ryan_mayu]

could you pls provide some sample data and expected output?

[Bourne_Xian]

Hi Ryan,

Thanks for your time.
You could find the sample data provided in another post which have extractly same issue: https://www.dropbox.com/s/fhbpj7pgragr2g6/Ranking%20test.pbix?dl=0

Expected output for 27_Cat is 25, while actual output is 8.


The expected output for 6_Cat is 6, while actual output is 5.