Forum Discussion
Create a calculated table to sum unique values.
I have a table as below.
I want to create a calculated table that calculates active score at account level for unique computer id and their max active_date. Meaning if for account 1000, computer id 1 has 2 on dates, 1-OCT and 3-OCT, I want to take value for only 3-OCT since that is the latest and sum its active_score with other computer id in the account and get result at account level.
This is what the output should look like.
There are some solutions in the community that I have tried but nothing seems to work. I can use both Power Query to create a new table or just create a new DAX table.
I've tried summarize formula as well but it did not work. Maybe something wrong here.
Hi, newpi , whether Power Query or DAX, either can easily do the trick.
-------------------------------Power Query-------------------------------
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIEYiMDIwNdQwNdA0OwQKwOdlljdFkjmKyBpa6RKZBjgCxrjKzXFK7XCIvJRnhljeEmG6HZi+TmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [accountid = _t, #"computer id" = _t, date = _t, active_score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"accountid", Int64.Type}, {"computer id", Int64.Type}, {"date", type date}, {"active_score", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"accountid", "computer id"}, {{"grouped", each [maxdate = List.Max([date]), score = _{[date = maxdate]}[active_score]][score]}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"accountid"}, {{"Total_active_score", each List.Sum([grouped])}}) in #"Grouped Rows1"-----------------------------DAX-----------------------------------
Total Active Score = SUMX ( VALUES ( 'DAX'[computer id] ), VAR __maxdate = CALCULATE ( MAX ( 'DAX'[date] ) ) RETURN CALCULATE ( MAX ( 'DAX'[active_score] ), 'DAX'[date] = __maxdate ) )
3 Replies
Hi, newpi , whether Power Query or DAX, either can easily do the trick.
-------------------------------Power Query-------------------------------
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIEYiMDIwNdQwNdA0OwQKwOdlljdFkjmKyBpa6RKZBjgCxrjKzXFK7XCIvJRnhljeEmG6HZi+TmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [accountid = _t, #"computer id" = _t, date = _t, active_score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"accountid", Int64.Type}, {"computer id", Int64.Type}, {"date", type date}, {"active_score", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"accountid", "computer id"}, {{"grouped", each [maxdate = List.Max([date]), score = _{[date = maxdate]}[active_score]][score]}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"accountid"}, {{"Total_active_score", each List.Sum([grouped])}}) in #"Grouped Rows1"-----------------------------DAX-----------------------------------
Total Active Score = SUMX ( VALUES ( 'DAX'[computer id] ), VAR __maxdate = CALCULATE ( MAX ( 'DAX'[date] ) ) RETURN CALCULATE ( MAX ( 'DAX'[active_score] ), 'DAX'[date] = __maxdate ) )newpi , Try a measure like
calculate(sumX(values(table[computer_id]),lastnonblankvalue(active_date,max(Table[active score]))),allexcept(table, table[computer_id]))
CNENFRNL Thanks. The DAX measure worked perfect. I couldn't understand the Power Query solution.
In the DAX measure, I used SUM instead of Max in the return value. Though Since its a single value per row, it doesn't make a difference I guess. Thanks a lot for your help.Total Active Score = SUMX ( VALUES ( 'DAX'[computer id] ), VAR __maxdate = CALCULATE ( MAX ( 'DAX'[date] ) ) RETURN CALCULATE ( SUM ( 'DAX'[active_score] ), 'DAX'[date] = __maxdate ) )
