Forum Discussion
Solved
Selecting max value for each row with the same condition
I have the following data:
| ID | Date |
| 01 | 4/5/2020 |
| 01 | null |
| 02 | 1/6/2021 |
| 03 | 2/3/2021 |
I want to create a second column Date2 that states the max date for each ID, like so:
| ID | Date | Date2 |
| 01 | 4/5/2020 | null |
| 01 | 1/1/9999 | null |
| 02 | 1/6/2021 | 1/6/2021 |
| 03 | 2/3/2021 | 2/3/2021 |
Is there a solution for this that doesn't involve Group By? To elaborate, my current method for doing this is:
- Replace null with a date far far into the future, e.g. 1/1/9999
- Duplicate the data table
- Group the duplicate table by max of Date to get Date2, which will ensure 1/1/9999 is selected should it exist for each Misc Value
- Merge the original with the duplicate
- Replace 1/1/9999 back with null
But I'm not really a fan of this rudimentary technique because it involves so many steps. Does a simpler, formula-based solution exist?
Try this calculated column (DAX):
Date2 = VAR vMaxDateNoBlank = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) ) VAR vRowCountWithBlank = CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[ID] ), ISBLANK ( Table1[Date] ) ) VAR vResult = IF ( vRowCountWithBlank > 0, BLANK (), vMaxDateNoBlank ) RETURN vResult
2 Replies
Try this calculated column (DAX):
Date2 = VAR vMaxDateNoBlank = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) ) VAR vRowCountWithBlank = CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[ID] ), ISBLANK ( Table1[Date] ) ) VAR vResult = IF ( vRowCountWithBlank > 0, BLANK (), vMaxDateNoBlank ) RETURN vResultHi bleow ，
You can create a measure as below to get it:
Date2 = VAR _count = COUNTX ( FILTER ( ALLSELECTED( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) && ISBLANK ( 'Table'[Date] ) ), [ID] ) RETURN IF ( _count > 0, BLANK (), CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) )Best Regards
