Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Issue with RANKX function with "Don't Summarize" option

I am facing the issue while ranking the data based on a "Date" column to get the top 2 categories for each category using the RANKX function. Below is the sample data: I am getting the below o...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    Create a measure 'Rank' as below, then sort by column rank:

    Rank =
RANKX (
    GROUPBY ( ALLSELECTED ( 'TestData' ), TestData[Category], TestData[Date] ),
    RANKX (
        ALLSELECTED ( TestData ),
        CALCULATE ( MAX ( TestData[Category] ) ),
        ,
        ASC,
        DENSE
    )
        + RANKX (
            GROUPBY ( ALLSELECTED ( 'TestData' ), TestData[Category], TestData[Date] ),
            CALCULATE ( MAX ( 'TestData'[Date] ) ),
            ,
            ASC,
            DENSE
        )
            / CALCULATE ( COUNTROWS ( 'TestData' ), ALLSELECTED () ),
    ,
    ASC,
    DENSE
)

    Please check my sample file for more details. If it doesn't work, share a pbix file on cloud storage for further research.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak - Amit Thanks for your help. As of now, it's working for me. Along with this, I want to sort one of the columns based on another column before giving the rank. Can you help me in that?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak - Amit can you help me to sort the data within the group as well?

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, Anonymous 

    You need to sort column 'Category' first, then hold down 'Shift' and click another column (column'Date'/column'Rank') for secondary sorting.

     

    Best Regards,
    Community Support Team _ Eason

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      v-easonf-msft - Can we do this using measure? Though your solution works for me but this is a sort of manual step.

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, Anonymous 

        Create a measure 'Rank' as below, then sort by column rank:

        Rank =
RANKX (
    GROUPBY ( ALLSELECTED ( 'TestData' ), TestData[Category], TestData[Date] ),
    RANKX (
        ALLSELECTED ( TestData ),
        CALCULATE ( MAX ( TestData[Category] ) ),
        ,
        ASC,
        DENSE
    )
        + RANKX (
            GROUPBY ( ALLSELECTED ( 'TestData' ), TestData[Category], TestData[Date] ),
            CALCULATE ( MAX ( 'TestData'[Date] ) ),
            ,
            ASC,
            DENSE
        )
            / CALCULATE ( COUNTROWS ( 'TestData' ), ALLSELECTED () ),
    ,
    ASC,
    DENSE
)

        Please check my sample file for more details. If it doesn't work, share a pbix file on cloud storage for further research.

         

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.