Replicate DAX SUMX with Multiple IFs in POWER QUERY

[Anonymous]

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)

[dax · Accepted solution]

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.

[dax]

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]

@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]

@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)

[Anonymous]

@dax I tried modifying query to handle null values but column result is showing all null. I believe I'm close to resolution but formula is missing something....fyi all columns in the formula are Data Type: Whole Numbers

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

[dax · Accepted solution]

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 · Accepted solution]

@dax that worked, thank you very much:)