Ranking date and breaking ties ?

[eden]

Hello 

 how to rank this table   based on start date excluding status = published 

Workorder	Status	customer	start date	End Date
One	published	abc	01/08/2020	24/09/2020
two	reviewed	def	24/09/2020	01/11/2020
three	waiting	xyz	17/09/2020	12/01/2021
four	defered	abc	01/12/2020	03/06/2021
five	published	oiu	01/07/2020	23/12/2021
six	in progress	abc	22/05/2019	01/09/2020
seven	in progress	def	21/07/2019	21/08/2020
eight	in progress	kjs	21/08/2019	12/11/2020

 If the date are same ties needs to be broken using the end date 

Regards

[eden]

@ harshnathani Check the ranking 7 , different dates were ranked as same in order

[harshnathani]

Hi @eden ,

Ranking = IF( MAX('Table'[Status]) = "published" , BLANK(),

RANKX(FILTER(ALL('Table'),'Table'[Status] <> "published"),CALCULATE(MAX('Table'[start date]))))

If this is not the output you desire, please share the output in the sample you have provided.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[eden]

@harshnathani 

How to break the ties ? 

[harshnathani · Accepted solution]

Hi @eden ,

Create a measure

BREAK = RANKX(FILTER(ALL('Table'),'Table'[start date] = MAX('Table'[start date])),CALCULATE(MAX('Table'[End Date])))

Then create the RANK measure

Ranking = IF( MAX('Table'[Status]) = "published" , BLANK(),

RANKX(FILTER(ALL('Table'),'Table'[Status] <> "published"),CALCULATE(MAX('Table'[start date])+ [BREAK])))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[eden]

@ harshnathani

[harshnathani]

Hi @eden ,

This is because your start date and end date for 2 rows are same.

Try adding RAND() to break the ties.

Ranking = IF( MAX('Table'[Status]) = "published" , BLANK(),

RANKX(FILTER(ALL('Table'),'Table'[Status] <> "published"),CALCULATE(MAX('Table'[start date])+ [BREAK]+ RAND())))

[amitchandak]

@eden , add a very small duration using random number generator and try

like rank on [End Date]+ time(0,0,RAND() )

refer this https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...