Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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!
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |