Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

M-Query for given DAX query

Hi, I want to convert the given DAX query into M-Query. Please help me ! Thanks

 

CALCULATE(
        SUM('Table'[Values]);
        FILTER(ALLSELECTED('Table'); 'Table'[Date] <= SELECTEDVALUE('Table'[Date]))
    )
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Anonymous ,

    You use SELECTEDVALUE in the DAX formula, so the output of this formula will be dynamic. If the formula is converted to M-Query, the result will be static. Do you want to use the parameters in the query editing? Could you share your expected output?

    If you want to create “Running Totals in Power Query”, here are two ways:

    1. Reference this article: Create Running Totals in Power Query

    (tbl as table, sumcolumn as text, rowindex as number) =>
let
    #"Removed Other Columns" = Table.SelectColumns(tbl,{sumcolumn, "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Index] <= rowindex),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"RunningTotal", each List.Sum([Temp]), type number}}),
    RunningTotal = Record.Field(#"Grouped Rows"{0},"RunningTotal")
in
    RunningTotal
    let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Running Total", each fnRunningTotal(Source,"Value",[Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

    2. Reference this post: How to do a running Sum by group in Power Query?

    (MyTable as table) as table =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    Cumulative = List.Accumulate(Source[Value],{},(cumulative,cost) => cumulative & {List.Last(cumulative, 0) + cost}),
    AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningSum

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZhrEYRONqpPgcv7rjySfNwBwakPNobgB1F-YZ4I6W8QXQ?e=85jWQW

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Please see the attached file with the solution, the file includes two Measures as below. 

    Cumulative Sum of Log = 
VAR _maxIndex = MAX( 'Table'[Index] )
RETURN  
CALCULATE( 
    SUM( 'Table'[LogVal] ),
    ALLSELECTED( 'Table' ),
    VALUES( 'Table'[Groups] ),
    'Table'[Index] <= _maxIndex
)

    and 

    Exp(N)-1 = 
VAR _maxIndex = MAX( 'Table'[Index] )
RETURN  
CALCULATE( 
    ( EXP( SUM( 'Table'[LogVal] ) ) -1 ), 
    ALLSELECTED( 'Table' ),
    VALUES( 'Table'[Groups] ),
    'Table'[Index] <= _maxIndex
)​
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

     

4 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi Anonymous ,

    You use SELECTEDVALUE in the DAX formula, so the output of this formula will be dynamic. If the formula is converted to M-Query, the result will be static. Do you want to use the parameters in the query editing? Could you share your expected output?

    If you want to create “Running Totals in Power Query”, here are two ways:

    1. Reference this article: Create Running Totals in Power Query

    (tbl as table, sumcolumn as text, rowindex as number) =>
let
    #"Removed Other Columns" = Table.SelectColumns(tbl,{sumcolumn, "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Index] <= rowindex),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"RunningTotal", each List.Sum([Temp]), type number}}),
    RunningTotal = Record.Field(#"Grouped Rows"{0},"RunningTotal")
in
    RunningTotal
    let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Running Total", each fnRunningTotal(Source,"Value",[Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

    2. Reference this post: How to do a running Sum by group in Power Query?

    (MyTable as table) as table =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    Cumulative = List.Accumulate(Source[Value],{},(cumulative,cost) => cumulative & {List.Last(cumulative, 0) + cost}),
    AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningSum

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZhrEYRONqpPgcv7rjySfNwBwakPNobgB1F-YZ4I6W8QXQ?e=85jWQW

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, Thanks for your reply and detailed explanation. 

      As you have mentioned i need dynamic solution with dates and other filters as well. 

       

      So i guess i need to do it in the DAX only but in DAX the formula is not giving output correctly.

       

      Following is the DAX query

       

       

      LogVal = LOG(1+Sheet1[Value])

%CumVal =
CALCULATE(
(exp(sum(Sheet1[LogVal]))-1)*100,
FILTER(ALLSELECTED(Sheet1), Sheet1[Index]<=Max(Sheet1[Index])
)
)​

       

       

      Following are some tables for original data and desired output and wrong output by DAX, Could you please throw some light on this how I can get correct output? Thanks

       

        

      Power Bi Table
      IndexDateGroupsAttributeValue
      11/11/2019 0:00A1Score11
      11/11/2019 0:00A1Score24
      21/12/2019 0:00A1Score14
      21/12/2019 0:00A1Score28
      31/13/2019 0:00A1Score115
      31/13/2019 0:00A1Score25
      41/14/2019 0:00B1Score12
      41/14/2019 0:00B1Score29
      51/15/2019 0:00B1Score14
      51/15/2019 0:00B1Score26
      61/16/2019 0:00C1Score16
      61/16/2019 0:00C1Score24
      71/17/2019 0:00C1Score19
      71/17/2019 0:00C1Score27
      81/18/2019 0:00C1Score18
      81/18/2019 0:00C1Score25
      91/19/2019 0:00C1Score14
      91/19/2019 0:00C1Score26
           

       

      Desired Output is as follows, 

       

       

      PowerBI is giving following wrong output

       

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi Anonymous 

         

        Please see the attached file with the solution, the file includes two Measures as below. 

        Cumulative Sum of Log = 
VAR _maxIndex = MAX( 'Table'[Index] )
RETURN  
CALCULATE( 
    SUM( 'Table'[LogVal] ),
    ALLSELECTED( 'Table' ),
    VALUES( 'Table'[Groups] ),
    'Table'[Index] <= _maxIndex
)

        and 

        Exp(N)-1 = 
VAR _maxIndex = MAX( 'Table'[Index] )
RETURN  
CALCULATE( 
    ( EXP( SUM( 'Table'[LogVal] ) ) -1 ), 
    ALLSELECTED( 'Table' ),
    VALUES( 'Table'[Groups] ),
    'Table'[Index] <= _maxIndex
)​
        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski