cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI2B Frequent Visitor
Frequent Visitor

Rewriting formula DAX in the language M

Hi
Need help rewriting formula dax in the language m - add a custom column
my formula: Previous = LOOKUPVALUE('445'[datetime],'445'[Rank],'445'[Rank]-1)
goal: append two queries (with the participation of the column)
similar theme unanswered: 
http://community.powerbi.com/t5/Desktop/How-to-append-queries-to-calculated-columns/m-p/40520#U40520


It is also open to other solutions
Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Rewriting formula DAX in the language M

Hi @BI2B,

 

In your scenario, you can use UI to generate the backend M query.

 

1. Add a index column from 0.

2. Add a custom column like below:

=AddInd[datetime]{[Index]-1}

3. Replace the error with null value.

4. The backend M query like below:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM1NNI1VNJRAmMDpVgdhLARUAiMUYWNgUJgjCpsAhQCY6BwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datetime = _t, Rank = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetime", type date}, {"Rank", Int64.Type}, {"Amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Rank", Order.Ascending}}),
    AddInd = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddInd, "Custom", each AddInd[datetime]{[Index]-1}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}})
in
    #"Replaced Errors"

 

Reference:

Absolute and Relative References in Power Query: R1C1 Excel-style approach

How can I do calculation based on next/prior row in Power Query?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Moderator v-qiuyu-msft
Moderator

Re: Rewriting formula DAX in the language M

Hi @BI2B,

 

In your scenario, you can use UI to generate the backend M query.

 

1. Add a index column from 0.

2. Add a custom column like below:

=AddInd[datetime]{[Index]-1}

3. Replace the error with null value.

4. The backend M query like below:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM1NNI1VNJRAmMDpVgdhLARUAiMUYWNgUJgjCpsAhQCY6BwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datetime = _t, Rank = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetime", type date}, {"Rank", Int64.Type}, {"Amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Rank", Order.Ascending}}),
    AddInd = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddInd, "Custom", each AddInd[datetime]{[Index]-1}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}})
in
    #"Replaced Errors"

 

Reference:

Absolute and Relative References in Power Query: R1C1 Excel-style approach

How can I do calculation based on next/prior row in Power Query?

 

Best Regards,
Qiuyun Yu

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

Re: Rewriting formula DAX in the language M

Hi Qiuyun,

Thank you very much for your help and solution Smiley Happy