Need help to convert DAX to M Code

[umasankar]

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. 

[umasankar]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected end result.

[v-chenwuz-msft]

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.

[BA_Pete]

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