Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Vivek_KV_
Frequent Visitor

Custom Column which referring to it's own while creating

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...
BI.PNG

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




3 REPLIES 3
v-yohua-msft
Community Support
Community Support

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

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

lbendlin
Super User
Super User

 

 

 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.

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.