Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
Employee
Employee

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.