Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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,
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
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
84 | |
84 | |
67 | |
63 | |
62 |
User | Count |
---|---|
208 | |
121 | |
112 | |
79 | |
70 |