Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |