Forum Discussion
DAX Groupby Records - Only include most recent item.
Hello all,
I have a set of audit tables, which show changes over time to data.
I am trying to group by the users in a particular age range, but only count the row with the most recent record.
Could anyone point me in the direction of how this is possible in PowerBI?
A copy of the data set can be found here:
https://imgur.com/a/GJcb9wN
A sample workbook can be found here:
http://s000.tinyupload.com/?file_id=49799153102272249392
Many thanks for your time!
Hi Anonymous ,
Had to think about how to approach this, but figured one out. First off, a big thank you for supplying a test pbix, that saves us a lot of time in helping you 🙂
Anway, I used the following measure:
Count Of Latest = VAR _tempTable = ADDCOLUMNS(FILTER(test, test[ID] <> BLANK()), "IsLatest", VAR _curID = [ID] VAR _curDate = [date] RETURN IF(COUNTROWS(FILTER(ALL(test), test[ID] = _curID && test[date] > _curDate)) = 0, TRUE, FALSE)) RETURN COUNTROWS(FILTER(_tempTable, [IsLatest] = TRUE()))First, I create a temporary table that starts with a filtered version of the table (filtering out blanks, they are useless?) and adds a column "IsLatest" that returns True if that row of the temp table is the latest row for that ID and false if not. The return value is the count of rows that has IsLatest is TRUE. Putting this in your visual, results to this:
The PBIX can be found here:
https://1drv.ms/u/s!Ancq8HFZYL_aiIxHaIgcyrtlh1m6Bg?e=aFu6Yu
Does this suits your needs? Let me know if you have any more questions 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
1 Reply
- JarroVGIT
Resident Rockstar
Hi Anonymous ,
Had to think about how to approach this, but figured one out. First off, a big thank you for supplying a test pbix, that saves us a lot of time in helping you 🙂
Anway, I used the following measure:
Count Of Latest = VAR _tempTable = ADDCOLUMNS(FILTER(test, test[ID] <> BLANK()), "IsLatest", VAR _curID = [ID] VAR _curDate = [date] RETURN IF(COUNTROWS(FILTER(ALL(test), test[ID] = _curID && test[date] > _curDate)) = 0, TRUE, FALSE)) RETURN COUNTROWS(FILTER(_tempTable, [IsLatest] = TRUE()))First, I create a temporary table that starts with a filtered version of the table (filtering out blanks, they are useless?) and adds a column "IsLatest" that returns True if that row of the temp table is the latest row for that ID and false if not. The return value is the count of rows that has IsLatest is TRUE. Putting this in your visual, results to this:
The PBIX can be found here:
https://1drv.ms/u/s!Ancq8HFZYL_aiIxHaIgcyrtlh1m6Bg?e=aFu6Yu
Does this suits your needs? Let me know if you have any more questions 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂