Forum Discussion

umasankar's avatar
umasankar
Frequent Visitor
4 years ago

Need help to convert DAX to M Code

Hi,

 

I am having a requiremnet to fulfill the client request. For this i need to create a calculated mesures later need to pivot those measures. below is the calculated measure.

TotalDef = CALCULATE(SUM('SAP Feed'[Quantity]),FILTER('SAP Feed',('SAP Feed'[Account (Planning) (Text)]="Def - Export Planned" || 'SAP Feed'[Account (Planning) (Text)]="Def - Export UnPlanned")))
 
Like the above we need to craete another 2 measures and after that need to pivot these three. Pivot is only available in power Query right. so i want convert those DAX measures to calculated column in M query.
 
cananyone help me please. 

 

Need Help

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi umasankar ,

     

    Not sure if I've fully understood your requirements here, but if you want to 'pivot' or display data in different formats, you can just use the matrix or table visual.

     

    Pete

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

    Hi umasankar ,

     

    You can first filter the table and use groupby in power query edit.

    This the code you can refer:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjQwMFTSUXJJTVPQVXCtKMgvKlEIyEnMy0tNAQobKsXqEFJjhFNNaB5ClTFRqkyIUmWKrMq/JCO1CEibwQSNcDvVnAg1FjjVIDvBkihVhgbEKTNEVgbzkCEwYGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Account (Planning) (Text)" = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Account (Planning) (Text)", type text}, {"Quantity", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Account (Planning) (Text)"] <> "Other")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Account (Planning) (Text)"]), "Account (Planning) (Text)", "Quantity", List.Sum)
in
    #"Pivoted Column"

     

    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.

  • umasankar's avatar
    umasankar
    Frequent Visitor
    4 years ago

    Hi Everyone,

     

    Thanks for all your replies. As of now i received the conformation that it is not possible for my scenario. So i am giving alternative solution to the client.