Solved: Converting DAX to M

SzEmo · ‎01-26-2022

Dear All,

Could you please help me with converting the following DAX statement to PowerQuery (M)?

= CALCULATE(

SUM(Database[Column1]),

Database[Column2] IN {"C/R", "no"},

Database[Column3] IN {"OPEN", "CLOSED"},

Database[Column4] IN {"1"},

Database[Column5] = "MAT-E")

Thank you in advance!

v-chenwuz-msft · ‎01-31-2022

Hi @SzEmo ,

Maybe you can try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLWDwKS/gGufkAKJODrGKLrqhSrE61kBJd29vEPdnUBMoxQFBgDeXn5mNpTwLImMFm4blTjTYG8ytRihHZUw83g0hj6gebHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "if", each if List.Contains({"C/R","no"},[Column2]) 
    and List.Contains({"OPEN","CLOSED"},[Column3]) 
    and List.Contains({"1"},[Column4]) 
    and List.Contains({"MAT-E"},[Column5])
    then 1 else 0),
    
    #"Grouped Rows" = Table.Group(#"Added Custom", {"if"}, {{"sum", each List.Sum([Column1]), type nullable number}}),
    #"1" = #"Grouped Rows"{[#"if"=1]}[sum]
in
    #"1"

Best Regards

Community Support Team _ chenwu zhu

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

v-chenwuz-msft · ‎01-31-2022

Hi @SzEmo ,

Maybe you can try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLWDwKS/gGufkAKJODrGKLrqhSrE61kBJd29vEPdnUBMoxQFBgDeXn5mNpTwLImMFm4blTjTYG8ytRihHZUw83g0hj6gebHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "if", each if List.Contains({"C/R","no"},[Column2]) 
    and List.Contains({"OPEN","CLOSED"},[Column3]) 
    and List.Contains({"1"},[Column4]) 
    and List.Contains({"MAT-E"},[Column5])
    then 1 else 0),
    
    #"Grouped Rows" = Table.Group(#"Added Custom", {"if"}, {{"sum", each List.Sum([Column1]), type nullable number}}),
    #"1" = #"Grouped Rows"{[#"if"=1]}[sum]
in
    #"1"

Best Regards

Community Support Team _ chenwu zhu

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

KNP · ‎01-26-2022

Some more detail please. Can you paste in some sample data, in the form of a table?

Can you add some detail around what/why you are trying to do this? Desired outcome?

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.