Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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.
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?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |