cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Erik8309
New Member

Group by columns and select a value by latest time/date

Hi,

 

So I have a table with username, majorversion, time and buildname.

There are more than 1 row with username and majorversion and I want to figure out what the latest reported buildname is, 
So I want to group by username and majorversion and order the list per group by time and select the last buildname. 

 

This seems like an easy task, but Im having trouble doing that using the group by feature in the query editor.
The logical way to me would be to add an aggregation with the Max value of the time column and then get the Buildname for that row somehow, but that seems impossible. 

 

Any ideas? 

 

Thanks!

 

/Erik 

 

 

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft
Microsoft

Try creating a summary table (as a calculated table) in DAX

 

Something like

 

New Table = 
VAR S1 = SELECTCOLUMNS(
            SUMMARIZECOLUMNS(
			'Table1'[username] ,
			'Table1'[majorversion] ,
			-- Aggregated Col --
            "Max time" , MAX('Table1'[time]) 
            )
            ,
			"Max time" , [Max time],
            "Max UserName" , [username] ,
            "Max Majorversion" ,[majorversion])
			
VAR S2 = SELECTCOLUMNS(
                 FILTER(CROSSJOIN(S1,Table1),[Max time] = [time]) ,
                 "UserName" , [username] ,
                 "MajorVersion" , [majorversion] ,
                 "Time" , [Max time] ,
                 "BuildName", [buildname]
             )
RETURN S2

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Phil_Seamark
Microsoft
Microsoft

Try creating a summary table (as a calculated table) in DAX

 

Something like

 

New Table = 
VAR S1 = SELECTCOLUMNS(
            SUMMARIZECOLUMNS(
			'Table1'[username] ,
			'Table1'[majorversion] ,
			-- Aggregated Col --
            "Max time" , MAX('Table1'[time]) 
            )
            ,
			"Max time" , [Max time],
            "Max UserName" , [username] ,
            "Max Majorversion" ,[majorversion])
			
VAR S2 = SELECTCOLUMNS(
                 FILTER(CROSSJOIN(S1,Table1),[Max time] = [time]) ,
                 "UserName" , [username] ,
                 "MajorVersion" , [majorversion] ,
                 "Time" , [Max time] ,
                 "BuildName", [buildname]
             )
RETURN S2

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.