Please help converting DAX to M

[Rachel_123]

Hi, I've been struggling with a problem for awhile and finally found a workaround but to implement the solution I need to create a table in Power query in M, can anyone help me please translate the following dax to M:

Projects Summary = SUMMARIZE('Portfolio Reporting','Portfolio Reporting'[Project],'Portfolio Reporting'[Expense Type], 'Portfolio Reporting'[Date],"Actuals",CALCULATE(sum('Portfolio Reporting'[Value]),'Portfolio Reporting'[Versions] IN { "Actuals" }),"Current Budget", CALCULATE(SUM('Portfolio Reporting'[Value]),'Portfolio Reporting'[Versions] IN { "Current Budget" }),"Current Forecast",CALCULATE( SUM('Portfolio Reporting'[Value]),'Portfolio Reporting'[Versions] IN { "Current Fcst" }))

Thank you!

[JoZu]

Could you please provide some sample data with expected results. I think it would make it easier to find a working solution.

Best regards

[Rachel_123 · Accepted solution]

somebody gave me a script that worked

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Project", "Expense Type", "Date"}, {
    {"Actuals", each List.Sum(Table.SelectRows(_, each [Versions] = "Actuals")[Value])},
    {"Current Budget", each List.Sum(Table.SelectRows(_, each [Versions] = "Current Budget")[Value])},
    {"Current Forecast", each List.Sum(Table.SelectRows(_, each [Versions] = "Current Fcst")[Value])}
    })
in #"Grouped Rows"

[JoZu]

Hi @Rachel_123 

Try the following:

let
    Source = #"Portfolio Reporting",
    #"Grouped Rows" = Table.Group(Source, {"Project", "Expense Type", "Date"}, {{"Actuals", each List.Sum(List.Select([Value], each [Versions] = "Actuals")), type nullable number}, {"Current Budget", each List.Sum(List.Select([Value], each [Versions] = "Current Budget")), type nullable number}, {"Current Forecast", each List.Sum(List.Select([Value], each [Versions] = "Current Fcst")), type nullable number}})
in
    #"Grouped Rows"

I dont have any data to try it, sorry. But let me know if it works 🙂

Best regards

[Rachel_123]

Thank you so much, so this works for all the columns except the ones we are summarizing (Actuals, Current budget and Current forecast) I get error on those, something about cannot apply field access