cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Erik8309 Occasional Visitor
Occasional Visitor

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

Accepted Solutions
Super User
Super User

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

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!

2 REPLIES 2
Super User
Super User

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

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!

Super User
Super User

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

Hi,

 

Share some data and show the expected result.