Return text from different column based on criteria

[mryoan04]

Hi, experts, need your help here.
I have below table. I want to fill the last two columns that evaluate the product condition based on "Is Scrapped" and "Scrap Reason" from Transaction = Work Order.
So for example, if Product A has "Y" on WO transaction, i would like to return "Y" as well for every invoices rows of Type A in column "Is Scraped on Invoice". The same with column "Scrap Reason on Invoice" (if Scrap Reason column is not blank, I would have that value on every invoice rows of Type A).

Product	Transaction	Date	Is Scraped	Scrap Reason	Column Wanted (Is Scraped on Invoice)	Column Wanted (Scrap Reason on Invoice)
A	Invoice	7/1/2024			Y	DBR
A	Invoice	7/3/2024			Y	DBR
A	WO	7/2/2024
A	WO	7/4/2024	Y	DBR
B	Invoice	8/1/2024			Y	Bad Quality
B	Invoice	8/2/2024			Y	Bad Quality
B	Invoice	8/3/2024			Y	Bad Quality
B	WO	8/4/2024	Y	Bad Quality

Thank you very much for your help!!

[rajendraongole1 · Accepted solution]

Hi @mryoan04 - you can create a calculated column to fill the "Is Scraped on Invoice" column based on the "WO" transactions as:

Is Scraped on Invoice =
VAR CurrentProduct = 'Workforce'[Product]
VAR IsScrappedWO =
CALCULATE(
MAX('Workforce'[Is Scraped]),
FILTER(
'Workforce',
'Workforce'[Product] = CurrentProduct &&
'Workforce'[Transaction] = "WO" &&
'Workforce'[Is Scraped] = "Y"
)
)
RETURN
IF(
'Workforce'[Transaction] = "Invoice" &&
NOT ISBLANK(IsScrappedWO),
IsScrappedWO,
BLANK()
)

To fill the "Scrap Reason on Invoice" column based on the "Work Order" transactions

Scrap Reason on Invoice =
VAR CurrentProduct = 'Workforce'[Product]
VAR ScrapReasonWO =
CALCULATE(
MAX('Workforce'[Scrap Reason]),
FILTER(
'Workforce',
'Workforce'[Product] = CurrentProduct &&
'Workforce'[Transaction] = "WO" &&
NOT ISBLANK('Workforce'[Scrap Reason])
)
)
RETURN
IF(
'Workforce'[Transaction] = "Invoice" &&
NOT ISBLANK(ScrapReasonWO),
ScrapReasonWO,
BLANK()
)

this works. please check

[mryoan04]

Thank you! It works!