Forum Discussion

mryoan04's avatar
mryoan04
Frequent Visitor
2 years ago
Solved

Return text from different column based on criteria

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).

 

ProductTransactionDateIs ScrapedScrap ReasonColumn Wanted (Is Scraped on Invoice)Column Wanted (Scrap Reason on Invoice)
AInvoice7/1/2024  YDBR
AInvoice7/3/2024  YDBR
AWO7/2/2024    
AWO7/4/2024YDBR  
BInvoice8/1/2024  YBad Quality
BInvoice8/2/2024  YBad Quality
BInvoice8/3/2024  YBad Quality
BWO8/4/2024YBad Quality  

 

Thank you very much for your help!!

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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