Forum Discussion

SzEmo's avatar
SzEmo
New Member
4 years ago
Solved

Converting DAX to M

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!
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

2 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    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?

     

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.