cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## DAX (add column) identify the max date of grouping

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?

Mark Lovejoy

1 ACCEPTED SOLUTION
Solution Sage

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!

2 REPLIES 2
Super User

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

Solution Sage

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.