Ranking based on multiple criteria

Question

Hi All,&nbsp;&nbsp;I have the following data:&nbsp;NameStatusDateTimeA12020/01/05 17:59:57A12020/01/05 17:59:59A22020/01/05 17:59:35B12020/01/05 17:59:54B12020/01/05 17:59:55&nbsp;I need to rank my table based on status and date.&nbsp;I want to rank my table based on status = 1 with Desc datetime.&nbsp;So my O/P should look like as follows:&nbsp;&nbsp;NameStatusDateRankA12020/01/05 17:59:591A12020/01/05 17:59:572B12020/01/05 17:59:553B12020/01/05 17:59:544&nbsp;I have tried the query which can rank based on the date (Desc) but as soon as I am clubbing it with filter all the records are getting ranked as "1".&nbsp;&nbsp;I can not add a column to the dataset no I need to restrict myself to the measure.&nbsp;&nbsp;Thanks

parry2k · Answer

Anonymous it is very well explained in this post, just follow the instructions on how to rank category and sub category which is the use case for you.
&nbsp;
Would appreciate Kudos 🙂 if my solution helped.

amitchandak · Answer

Referhttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures&nbsp;Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @ Thanks. My Recent Blog -https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739 https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

anonymous · Answer

parry2k,amitchandak: I tried that solution provided by and my query looks following:&nbsp;Ranking by Sub Category = &nbsp;&nbsp;&nbsp;&nbsp;RANKX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;--DateTime Column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WorkItemCompltd[operationutcdttm],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- Status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WorkItemCompltd[workitemstatus]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --datetime column (Filter1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WorkItemCompltd[operationutcdttm] = MAX(WorkItemCompltd[operationutcdttm])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ---Filter 2 (status)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(MAX(WorkItemCompltd[workitemstatus]))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;This ranks everything as 1. Please correct me if i am wrong somewhere.

parry2k · Answer

Anonymous try this. seems like you didn't try to read the post but just followed the dax, try to understand the concept.
&nbsp;
RANKX (
        FILTER(
            ALL(
               --DateTime Column
                WorkItemCompltd[Name],
               -- Status
                WorkItemCompltd[workitemstatus]
                ),
            --datetime column (Filter1)
            WorkItemCompltd[workitemstatus] = MAX(WorkItemCompltd[workitemstatus])
            ),
            ---Filter 2 (status)
            CALCULATE(MAX(WorkItemCompltd[operationutcdttm]))
            )
&nbsp;

anonymous · Answer

parry2k&nbsp;: With the last solution it ranks everything as "1".&nbsp;I went through the article again and now understood to get all the row we have two columns as input to ALL

Forum Discussion

Ranking based on multiple criteria

7 Replies

Related content

Sequential Order column based on multiple criteria

Ranking by Multiple Criteria

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Name	Status	DateTime
A	1	2020/01/05 17:59:57
A	1	2020/01/05 17:59:59
A	2	2020/01/05 17:59:35
B	1	2020/01/05 17:59:54
B	1	2020/01/05 17:59:55

Name	Status	Date	Rank
A	1	2020/01/05 17:59:59	1
A	1	2020/01/05 17:59:57	2
B	1	2020/01/05 17:59:55	3
B	1	2020/01/05 17:59:54	4