Forum Discussion

Cyriackpazhe's avatar
Cyriackpazhe
Icon for Helper III rankHelper III
1 year ago

RankX

Why am i getting the output like this
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Cyriackpazhe 

The issue you're encountering with the RANKX function in Power BI is likely due to the way the function is being used in conjunction with the ALL function. The ALL function removes all filters from the specified columns or tables, which can cause the RANKX function to rank all values as 1 because it is not considering any context for the ranking.

 

To fix this, you need to ensure that the RANKX function is applied in a context that allows it to rank the values correctly. 

DAX
RankX = RANKX(
ALL('Calendar'[Date], 'Calendar'[Quarter]),
[Total],
,
DESC,
DENSE
)

Cyriackpazhe's avatar
Cyriackpazhe
Icon for Helper III rankHelper III
1 year ago

I'm still getting incorrect result.Also if we do as you mentioned, won't the virtual table contain all possible combinations of year and quarter. The rank would be assigned as per its position in this entire set right

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Cyriackpazhe , hello bhanu_gautam, thank you for your prompt reply!

     

    Please try the following measure:

    MeasureTotal = SUM('Table'[Total])
    RankX = 
IF (
    ISINSCOPE ( 'Table'[Date].[Quarter] ),
    RANKX (
        FILTER (
             ALL('Table') ,
            YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
        ),[MeasureTotal],,DESC
                ,
        DENSE
    ),
    IF (
        ISINSCOPE ( 'Table'[Date].[Year] ),
        RANKX (
            ALL ( 'Table' ),
            CALCULATE (
                SUM ( 'Table'[Total] ),
                ALLEXCEPT ( 'Table', 'Table'[Date].[Year] )
            ),
            [MeasureTotal],
            DESC,
            DENSE
        ),
        BLANK ()
    )
)

    Result:

    Best regards,

    Joyce

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

    • Cyriackpazhe's avatar
      Cyriackpazhe
      Icon for Helper III rankHelper III
      1 year ago

      but why isn't the meausre i wrote working. It should be correct right

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Cyriackpazhe  , thank you for your feedback.

         

        If possible, please update the sample pbix file without any sensitive information for further troubleshooting.