topic Re: DAX (add column) identify the max date of grouping in DAX Commands and Tips

DAX (add column) identify the max date of grouping

mlovejoy — Fri, 16 Dec 2022 14:35:20 GMT

Hello community,

My problematic is straight forward I need to add DAX column. I have cirteria x, y and z. I can identify duplicate with this formula:

Duplicates count =

var x= Criteria
return
COUNTROWS(FILTER(Table,criteria = x)) this give me the number of duplicates (grouping).

Then for every duplicates I need to put a 1 to the most recent date of the grouping and a 0 to the oldest.

Is that even possible to do in DAX add Column?

Thanks for your help,

Mark Lovejoy

Re: DAX (add column) identify the max date of grouping

TomasAndersson — Fri, 16 Dec 2022 15:00:22 GMT

Hi!

You should be able to do something like this:

Most recent date = VAR __IsDuplicate = //Checks if row has a duplicate. If all rows has duplicates, this is not needed. If('Table'[Duplicates]>0,TRUE(),FALSE()) VAR __MaxDate = //Finds the most recent (MAX()) date for current criteria CALCULATE( max('Table'[Date]), FILTER(all('Table'),'Table'[Criteria] = EARLIER('Table'[Criteria]) ) ) return if(__IsDuplicate, //If current row is a duplicate if('Table'[Date] = __MaxDate, //And current row date is the most recent date for this criteria 1, //return 1 0 //else return 0 ), 0 else return 0 )

Hope this helps!

Re: DAX (add column) identify the max date of grouping

tamerj1 — Fri, 16 Dec 2022 15:37:23 GMT

Hi mlovejoy

please try

Result =
MAXX (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Criteria] ) ),
'Table'[Date]
)