Ranking based on multiple criteria

[Anonymous]

Hi All, 

I have the following data: 

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

I need to rank my table based on status and date. 

I want to rank my table based on status = 1 with Desc datetime. 

So my O/P should look like as follows: 

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

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". 

I can not add a column to the dataset no I need to restrict myself to the measure. 

Thanks

[v-xicai]

Hi  @Anonymous  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Anonymous ,

You may create rank using Column or measure like DAX below.

Column1= RANKX(FILTER(ALLSELECTED(Table1),Table1[Name]=EARLIER(Table1[Name])&&Table1[Status]=1),Table1[Date],,DESC, Dense)
 
Measure1= RANKX(FILTER(ALLSELECTED(Table1),Table1[Name]=MAX(Table1[Name])&&Table1[Status]=1),MAX(Table1[Date]),,DESC, Dense)

You can go through the link https://docs.microsoft.com/zh-cn/dax/rankx-function-dax to learn more about the RANKX .

Best Regards,

Amy

Community Support Team _ Amy

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

[amitchandak]

Refer

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

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-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[parry2k]

@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.

Would appreciate Kudos 🙂 if my solution helped.

[Anonymous]

@parry2k,amitchandak: I tried that solution provided by and my query looks following: 

Ranking by Sub Category =

    RANKX (

        FILTER(

            ALL(

               --DateTime Column

                WorkItemCompltd[operationutcdttm],

               -- Status

                WorkItemCompltd[workitemstatus]

                ),

            --datetime column (Filter1)

            WorkItemCompltd[operationutcdttm] = MAX(WorkItemCompltd[operationutcdttm])

            ),

            ---Filter 2 (status)

            CALCULATE(MAX(WorkItemCompltd[workitemstatus]))

            )

This ranks everything as 1. Please correct me if i am wrong somewhere.

[parry2k]

@Anonymous try this. seems like you didn't try to read the post but just followed the dax, try to understand the concept.

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]))
            )

[Anonymous]

@parry2k : With the last solution it ranks everything as "1". 

I went through the article again and now understood to get all the row we have two columns as input to ALL