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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ivo_Kessler
Regular Visitor

Populate column based on single value from other column

Hi all,

 

I have a problem I already tried a lot to solve it but can´t find a solution.

In the table below "Entry No_" is a index value.

What I already figured out was how to populate "ILE 1". This is the highest "Entry No_" for the same "Serial No_".

I used:

ILE 1 = CALCULATE(MAX('F_Item Ledger Entries'[Entry No_]),ALLEXCEPT('F_Item Ledger Entries','F_Item Ledger Entries'[Serial No_]))

 

No I need something similar:

if the "Status" is "Scrap" i need to populate "ILE 2 for all previous lines with the same "Serial No_" with the "Entry No_"  (initial table is pretty huge with many "Serial No_")

What I need is colored in "red".

 

Any help is highly appreciated.

Thanks

 

Ivo

 

2022-03-24_18-55-48.jpg

 

1 ACCEPTED SOLUTION
Ivo_Kessler
Regular Visitor

Hi,

 

Power Query would just be to slow... the table is pretty big (almost 1.4 millon lines and growing).

I figured it out on how I can do it in a calculated column:

 

ILE 2 =

Var ILE_NR =
CALCULATE (
MAX ( 'F_Item Ledger Entries'[Entry No_] ),
ALLEXCEPT ( 'F_Item Ledger Entries', 'F_Item Ledger Entries'[Serial No_] ), 'F_Item Ledger Entries'[Status] = "Scrap"
)
RETURN
if (ILE_NR >= [Entry No_], ILE_NR, BLANK())
 
Thanks anyway.
Ivo

View solution in original post

2 REPLIES 2
Ivo_Kessler
Regular Visitor

Hi,

 

Power Query would just be to slow... the table is pretty big (almost 1.4 millon lines and growing).

I figured it out on how I can do it in a calculated column:

 

ILE 2 =

Var ILE_NR =
CALCULATE (
MAX ( 'F_Item Ledger Entries'[Entry No_] ),
ALLEXCEPT ( 'F_Item Ledger Entries', 'F_Item Ledger Entries'[Serial No_] ), 'F_Item Ledger Entries'[Status] = "Scrap"
)
RETURN
if (ILE_NR >= [Entry No_], ILE_NR, BLANK())
 
Thanks anyway.
Ivo
lbendlin
Super User
Super User

Does it have to be a calculated column or can it be done in Power Query?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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