Forum Discussion

Bourne_Xian's avatar
Bourne_Xian
Regular Visitor
1 year ago
Solved

Ranking value changed when filtering only one value

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/2981637

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

7 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    could you pls provide some sample data and expected output?

  • techies's avatar
    techies
    Super User
    1 year ago

    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()  
    )
     
     
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.