Forum Discussion

Rachel_123's avatar
Rachel_123
Icon for Helper I rankHelper I
3 years ago
Solved

Please help converting DAX to M

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!
Need Help
  • Rachel_123's avatar
    Rachel_123
    3 years ago

    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"


     

4 Replies

  • JoZu's avatar
    JoZu
    Frequent Visitor
    3 years ago

    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's avatar
      Rachel_123
      Icon for Helper I rankHelper I
      3 years ago

      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

       

       

  • JoZu's avatar
    JoZu
    Frequent Visitor
    3 years ago

    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's avatar
      Rachel_123
      Icon for Helper I rankHelper I
      3 years ago

      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"