cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
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:

image.png

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
JarroVGIT
Resident Rockstar
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:

image.png

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!