Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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 )
)
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 )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |