Forum Discussion

jaryszek's avatar
jaryszek
Icon for Super User rankSuper User
10 months ago
Solved

Why rankx function shows duplicated rank?

Hello,

I am using this 1 dax in order to rankx my resource groups:

Rank (Selected Category) = 
VAR cat = SELECTEDVALUE ( Fct_EA_AmortizedCosts_Daily[ResourceGroup] )
RETURN
IF (
    NOT ISBLANK ( cat ),
    VAR r =
        RANKX (
            ALL(Fct_EA_AmortizedCosts_Daily[ResourceGroup]),                         -- ignore slicer on category
            [Total Amortized Cost],
            ,
            DESC, Dense
        )
    RETURN IF ( r <= 5, r, 6 )
)


and without any selection my ranked is duplicated:



Why? If this is a matter of duplicated rows in fct amortized table?

Best,
Jacek



DAX
fact table
RANKX
  • Praful_Potphode's avatar
    Praful_Potphode
    10 months ago

    Hi jaryszek 
    try below measure:

    Rank = RANKX(
    'Table',
    [Total Amortized Cost]
    ,,DESC,
    Dense
    )

    also you can create visual calculation(Select table visual and click New Visual Calculation) like below:

    Rank = FORMAT(
    RANK(
        DENSE,ORDERBY([Total Amortized Cost],DESC)
        )
        ,"0"
        
)

    Try both options and let me know.

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

     

9 Replies

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    10 months ago

    Hi jaryszek 
    try below measure:

    Rank = RANKX(
    'Table',
    [Total Amortized Cost]
    ,,DESC,
    Dense
    )

    also you can create visual calculation(Select table visual and click New Visual Calculation) like below:

    Rank = FORMAT(
    RANK(
        DENSE,ORDERBY([Total Amortized Cost],DESC)
        )
        ,"0"
        
)

    Try both options and let me know.

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    10 months ago

    jaryszek 

     

    Yes, you have duplicate ResourceGroup values in your fact table. RANKX is evaluating each duplicate row separately.

     

    Use VALUES instead of ALL to get unique ResourceGroups:

     

    Rank (Selected Category) =
    VAR cat = SELECTEDVALUE(Fct_EA_AmortizedCosts_Daily[ResourceGroup])
    RETURN
    IF(
    NOT ISBLANK(cat),
    VAR r = RANKX(
    VALUES(Fct_EA_AmortizedCosts_Daily[ResourceGroup]),
    [Total Amortized Cost],
    ,
    DESC,
    Dense
    )
    RETURN IF(r <= 5, r, 6)
    )

     

    If this answer helped, please click Kudos or mark as Solution.
    -Kedar
    LinkedIn: https://www.linkedin.com/in/kedar-pande

    • jaryszek's avatar
      jaryszek
      Icon for Super User rankSuper User
      10 months ago

      Thank you,

      It didn't work:

       



      it is jsut repeating 1 for all records...

      Best,
      Jacek

  • Gabry's avatar
    Gabry
    Icon for Super User rankSuper User
    10 months ago

    Hey, why don't you try to use skip instead of dense?

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 months ago

        Hi jaryszek,

        Thanks for the update. It looks like the ranks are showing all as 1 because the DAX might have been created as a column instead of a measure, or the ALL() function is missing in your formula.

        I have reproduced your requirement and implemented the solution. I have attached the PBIX file for your review so you can see the updates. Let me know if you need any changes or further improvements.

        Regards,
        Community support Team.