DAX Calculate - Filter max date on several columns

[NickOIT]

Hi,

I'm struggling to figure out how to filter out duplicate rows based on the latest run date for each program and computer.

I think I'm almost there but can't quite figure it out.

Here's an example::

ClientID	ProgramName	LastRun
1	Program1	2017-12-20
1	Program1	2017-11-01
1	Program2	2017-12-21
2	Program1	2016-01-01

What I'm trying for:

ClientID	ProgramName	LastRun
1	Program1	2017-12-20
1	Program2	2017-12-21
2	Program1	2016-01-01

I found another thread with a similar issue request but they only had a single column that they had to match with the date column. The solution I found there works, but it only filters the latest date per client and not per client & program.

IsLatestChangedDateByID = Sheet1[LastRun] = 
	CALCULATE(
		MAX(Sheet1[LastRun]); 
		FILTER(ALL(Sheet1); Sheet1[ClientID]=EARLIER(Sheet1[ClientID]))
	)

Anybody got any ideas on how I can proceed?

Thanks for any help.

[Anonymous · Accepted solution]

@NickOIT,

Create your column using the dax below.

IsLatestChangedDateByID = 
	CALCULATE(
		MAX(Sheet1[LastRun]);
		FILTER(ALL(Sheet1); Sheet1[ClientID]=EARLIER(Sheet1[ClientID])&&Sheet1[ProgramName]=EARLIER(Sheet1[ProgramName]))
	)

Alternatively, right click your table and select "New Measure", apply DAX below.

Measure = MAX(Sheet1[LastRun])

Regards,
Lydia

[NickOIT]

Thank you so much! 
I was so close, must've written something wrong when I tried it yesteday.

I'll try your second suggestion as well after the holidays. 🙂

Merry Christmas!