Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Hi,
Share some data and show the expected result.
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |