Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replicate DAX SUMX with Multiple IFs in POWER QUERY

Hi, 

 

How do I replicate this DAX function in power query? Appreciate any help. Thanks.

 

SUMX('Scan Event Raw Data',IF('Scan Event Raw Data'[TimeACCEPtoAKMRFIDINACCEP]>0,'Scan Event Raw Data'[TimeACCEPtoAKMRFIDINACCEP],0)+IF('Scan Event Raw Data'[TimeACCEPtoCHMRFIDINACCEP]>0,'Scan Event Raw Data'[TimeACCEPtoCHMRFIDINACCEP],0)+IF('Scan Event Raw Data'[TimeACCEPtoMTMRFIDINACCEP]>0,'Scan Event Raw Data'[TimeACCEPtoMTMRFIDINACCEP],0)

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi qsmith83,

    You could try below M code, convert null to 0

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTICYkOlWB0IzwSIdY3gXEMgNgbzkkASpnC1YK4xRAzGRzUJogDGA8khSynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, c1 = _t, c2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"c1", Int64.Type}, {"c2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (if [c1]<0 or [c1]=null then 0 else [c1]) +(if [c2]<0 or [c2]=null then 0 else [c2])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"name"}, {{"sum", each List.Sum([Custom]), type number}, {"all", each _, type table [name=text, c1=number, c2=number, Custom=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"c1", "c2", "Custom"}, {"all.c1", "all.c2", "all.Custom"})
in
    #"Expanded all"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    6 years ago

    dax that worked, thank you very much:)

6 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi qsmith83, 

    You could try below M code to see whether it work or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTICYkOlWB0IzwSIdY3gXEMgNgbzkkASpnC1YK4xRAzGh5oUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, c1 = _t, c2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"c1", Int64.Type}, {"c2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (if [c1]>0 then [c1] else 0) +(if [c2]>0 then [c2] else 0)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"name"}, {{"sum", each List.Sum([Custom]), type number}, {"all", each _, type table [name=text, c1=number, c2=number, Custom=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"c1", "c2", "Custom"}, {"all.c1", "all.c2", "all.Custom"})
in
    #"Expanded all"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      dax  I get an error after creating custom column [Time Accep To RFID IN Accep]

       

      Here is M code for custom column

       

      Do I need to Group column to get sum???

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        dax please ignore the SUM I no longer need it, I just need the power query forumula below to work. Currently its showing Error as per screenshot below. When I click on Error, it says Expression.Error: We cannot convert the value null to type Logical.

         

        (if [TimeACCEPtoAKMRFIDINACCEP]>0 then [TimeACCEPtoAKMRFIDINACCEP] else 0) + (if [TimeACCEPtoCHMRFIDINACCEP]>0 then [TimeACCEPtoCHMRFIDINACCEP] else 0) + (if [TimeACCEPtoMTMRFIDINACCEP]>0 then [TimeACCEPtoMTMRFIDINACCEP] else 0)