Dense Rank by ordering on 3 columns with a twist

[zazaalaza]

I am looking to calculate a dense rank as shown in the image. Order by [StartDate], [EndDate] and [Phase] and then rank on [ID], [Phase]. The issue is that the values in [Phase] are not necessarily are in Ascending order since the [StartDate] & [EndDate] takes precedent. 

How can I go about solving this?

[Anonymous · Accepted solution]

Hi@ zazaalaza,

According to your description, You want to group by [ID] field, then sort by [StartDate],[EndDate],[Phase] fields(priority: [StartDate]>[EndDate]>[Phase]) and count after that, and the [Phase] field is the same as The previous line is the same and remains unincremented..Right?

Here are the steps you can follow：

(1)This is my test data:

(2)We can sort [ID], [StartDate],[EndDate],[Phase] fields in Power Query Editor. Set priority: [ID]>[StartDate]>[EndDate]>[Phase]

Then it will Automatically generate M-statements:       = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"StartDate", Order.Ascending}, {"EndDate", Order.Ascending}, {"Phase", Order.Ascending}})

(3) We can click “Index Column – From 1” in “Add Column” to create a index column.After it,we can click “Apply and close” to submit the data to Power BI Desktop.

(4)We can create a calculated column:  “Count“

Count = 
var _current_phase='Table1'[Phase]
var _current_index='Table1'[Index]
var _cuurent_id='Table1'[ID]
var _pre_line_phase=MAXX( FILTER('Table1','Table1'[Index]=_current_index-1 && 'Table1'[ID]=_cuurent_id) ,'Table1'[Phase])
return
  IF( _current_phase= _pre_line_phase , 0,1)

(5) Finally, we create another calculated column so that we can meet your need:

Rank = 
var _first=MINX( FILTER('Table1','Table1'[ID]=EARLIER('Table1'[ID])) ,'Table1'[Index])
var _last= 'Table1'[Index]
return
  CALCULATE(SUM('Table1'[Count]),FILTER( ALL('Table1'),'Table1'[Index]>=_first &&'Table1'[Index] <=_last))

If this method dose not meet your needs, can you provide some special inputs and outputs so that we can help you better.

[daXtreme]

Hi @zazaalaza 

This kind of "ranking" (but it's not really real ranking) would be far easier to do in Power Query than in DAX. So, where do you want to do it?