cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
qsmith83 Member
Member

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)

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

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.

View solution in original post

qsmith83 Member
Member

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

@dax that worked, thank you very much:)

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

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.

qsmith83 Member
Member

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

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

 

Here is M code for custom column

custom column - Time ACCEP To RFID IN ACCEP.PNG

 

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

Group By.PNG

 

 

qsmith83 Member
Member

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

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

 

custom column error.PNG

 

qsmith83 Member
Member

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

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

Community Support Team
Community Support Team

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

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.

View solution in original post

qsmith83 Member
Member

Re: Replicate DAX SUMX with Multiple IFs in POWER QUERY

@dax that worked, thank you very much:)

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,578)