cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Sorry, but I don't understand what you mean. Could you please post a link to a sample file so that I can show you how it works?

Thx

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




xarius Regular Visitor
Regular Visitor

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Here is a link to my sample file (for some reason I can't just upload a file):

Sample File

 

It contains 2 tables - the sample data table is what I have to work with, the final table is what I'm trying to achieve.

 

I think the screenshot below is the easiest to understand though for the column I'm trying to create though.

 

 

 image.png

 

 

 

Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Oh, sorry - just recognized the problem now.

Yes, you have to perform some recursice operation here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqUYrViVYyAvIcwSxjOMsEyHICs0yBLGcwywzOMgeyXMAsCzjLEshyBbMMDVCMNzSEm2qIsMrQGGJDLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Label = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Label", type text}}),
    GenerateList = 
        List.Skip(
        List.Generate(
            ()=> [Result = 0, Counter = 0],
            each [Counter] <= Table.RowCount(#"Changed Type"),
            each [
                    Switch = [
                    Start = 0,
                    A = [Result] + 1,
                    B = [Result] + 2,
                    C = [Result] + 3
                    ],
                    Result = try Record.Field(Switch, #"Changed Type"[Label]{[Counter]}) otherwise [Result]-1,
                Counter = [Counter]+1
            ],
            each [Result])
            ,1),
    MergeColumns = Table.FromColumns(Table.ToColumns(Source) & {GenerateList}, Table.ColumnNames(Source) & {"ExpectedOutput"})
in
    MergeColumns

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




xarius Regular Visitor
Regular Visitor

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Perfect! Thank you!!

Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

You're welcome!

 

Just recognized that I missed a buffer for performance. For some reason, I cannot edit my answer above - so I will paste the new code here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqUYrViVYyAvIcwSxjOMsEyHICs0yBLGcwywzOMgeyXMAsCzjLEshyBbMMDVCMNzSEm2qIsMrQGGJDLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Label = _t]),
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Label", type text}})),
    GenerateList = 
        List.Skip(
        List.Generate(
            ()=> [Result = 0, Counter = 0],
            each [Counter] <= Table.RowCount(#"Changed Type"),
            each [
                    Switch = [
                    Start = 0,
                    A = [Result] + 1,
                    B = [Result] + 2,
                    C = [Result] + 3
                    ],
                    Result = try Record.Field(Switch, #"Changed Type"[Label]{[Counter]}) otherwise [Result]-1,
                Counter = [Counter]+1
            ],
            each [Result])
            ,1),
    MergeColumns = Table.FromColumns(Table.ToColumns(Source) & {GenerateList}, Table.ColumnNames(Source) & {"ExpectedOutput"})
in
    MergeColumns

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries