Forum Discussion
Logic to group rows as one batch based on created date and Rank
- 2 years ago
I cannot match your expected data as weeknum is giving the same for your rows 2 and above.
I created similar dataset and able to get these results
Using Dax Columns:Rank Column = RANKX ( 'Table', WEEKNUM('Table'[Date]),,ASC, Dense) Batch Name Column = "Batch " & [Rank Column] Current Batch Column = If ( [Rank Column] = MAX ( 'Table'[Rank Column] ) , "Yes", "No")I think it will be good to have measures for this scenario, so providing the syntax for that also:
Rank Measure = RANKX( allselected('Table'), WEEKNUM( CALCULATE( max('Table'[Date]))) ,,asc, dense) Batch Name Measure = "Batch " & [Rank Measure] Current Batch Measure = var _Max = MAXX ( all('Table'), [Rank Measure] ) RETURN If ( [Rank Measure] = _Max , "Yes", "No")If the source data and your expectation is per your post, then adjust to your needs the weeknum portion of DAX.
Hope it helps!
I cannot match your expected data as weeknum is giving the same for your rows 2 and above.
I created similar dataset and able to get these results
Using Dax Columns:
Rank Column = RANKX ( 'Table', WEEKNUM('Table'[Date]),,ASC, Dense)
Batch Name Column = "Batch " & [Rank Column]
Current Batch Column = If ( [Rank Column] = MAX ( 'Table'[Rank Column] ) , "Yes", "No")
I think it will be good to have measures for this scenario, so providing the syntax for that also:
Rank Measure = RANKX( allselected('Table'), WEEKNUM( CALCULATE( max('Table'[Date]))) ,,asc, dense)
Batch Name Measure = "Batch " & [Rank Measure]
Current Batch Measure =
var _Max = MAXX ( all('Table'), [Rank Measure] )
RETURN If ( [Rank Measure] = _Max , "Yes", "No")
If the source data and your expectation is per your post, then adjust to your needs the weeknum portion of DAX.
Hope it helps!