Custom Column which referring to it's own while creating

[Vivek_KV_]

Hello fellow community members,

I'm currently facing a challenge in applying a logic in DAX for a table with four columns: LINE_2, HDR, NEW_ACCT, and CHANGE. Specifically, I'm working on creating a new column named LINE_FLD.

The logic I'm trying to implement is as follows: I want LINE_FLD to be set to "Y" if either LINE_2 is "Y" or the previous row's LINE_FLD is "Y," and the rest of the three columns (HDR, NEW_ACCT, CHANGE) are all "N".

I've shared the advanced query and included screenshots illustrating the logic. I would greatly appreciate any assistance or insights you can provide to help me overcome this challenge...

NOTE : Please note that I'm dealing with a large dataset, so I'm looking for a DAX solution that avoids performance issues associated with line-by-line checking or indexing in Power Query.

Thank you in advance for your support!

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lPSQcOxOqiikVhFsatFiEaiiUZiVYsqGkmkCZE0cFkkigglvqBcFDMcIlFFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LINE_2 = _t, HDR = _t, NEW_ACCT = _t, CHANGE = _t, #"LINE_FLD/Target column" = _t])
in
Source

[v-yohua-msft]

Hi, @Vivek_KV_ 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[Vivek_KV_]

@v-yohua-msft  It's not resolved.

[lbendlin]

 want LINE_FLD to be set to "Y" if either LINE_2 is "Y" or the previous row's LINE_FLD is "Y," and the rest of the three columns (HDR, NEW_ACCT, CHANGE) are all "N".

You need to bring your own definition of what you mean by "previous row". Neither Power Query nor DAX does not do that for you, at least not in a usable way.

To make matters worse you are referencing the field that you are adding. That can only be done with List.Accumulate. It is impossible to do in DAX.

You say you are dealing with a large dataset.  How large?  Millions of rows? The operation will require half the square of your number of rows in computations.

Your logic already stumbles at the first hurdle.  For your first row there is no "Previous row"  but you have not specified how to handle that scenario.