Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Ranking based on date and status

Hi All,    I have data that looks like  Name Status datetime(UTC) A 1 2020/01/05 17:59:57 A 2 2020/01/05 17:59:52 A 2 2020/01/05 17:59:51 A 3 2020/01/05 17:59:50 B 2 ...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    Create a measure using dax below:

    Rank = 
RANKX (
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS ( FILTER ( 'Table', 'Table'[Status] = EARLIER ( 'Table'[Status] ) ) )
    ),
    CALCULATE ( ( SUM ( 'Table'[datetime(UTC)] ) ) ),
    ,
    DESC,
    DENSE
)

     

     

    Community Support Team _ Jimmy Tao

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

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
6 years ago

Anonymous ,

 

Create a measure using dax below:

Rank = 
RANKX (
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS ( FILTER ( 'Table', 'Table'[Status] = EARLIER ( 'Table'[Status] ) ) )
    ),
    CALCULATE ( ( SUM ( 'Table'[datetime(UTC)] ) ) ),
    ,
    DESC,
    DENSE
)

 

 

Community Support Team _ Jimmy Tao

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

  • AnkitBI's avatar
    AnkitBI
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi v-yuta-msft 

     

    I have tried your solution and it is working perfectly fine. However I am having issues understanding it. Can you explain the logic behind Filter part.

     

    Thanks

    Ankit Jain

      • AnkitBI's avatar
        AnkitBI
        Icon for Solution Sage rankSolution Sage
        6 years ago

        Hi v-yuta-msft 

         

        Thanks for the link. I actually figured out the nested filter concept. What I was struggling with was the use of CountRows as I thought second parameter in "Filter" should be a condition that returns a boolean value but it seems even 1 is an expectable value.

         

        Again thanks for this new learning 🙂

         

        Thanks

        Ankit Jain