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
newpi
Helper V
Helper V

Create a calculated table to sum unique values.

I have a table as below.
Screen Shot 2020-10-05 at 4.06.27 PM.png


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.

Screen Shot 2020-10-05 at 4.06.30 PM.png

 

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.

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2020-10-06 004228.png

 

 

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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 )
)
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2020-10-06 004228.png

 

 

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

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.