If statement returns value based on 3 criterias

[wotkara]

Good evening, struggling with this and have spent a week reading books/searching post/watching youtube & cannot figure it out. Please help.

Here are my relationship tables:

delivery_ticket_detail

DT	Item	Quantity	I Type	Delivery Date	Original Rental Start Date	Rental Stop Date	Return Date
58160	21767	1	R	4/30/2024	4/30/2024

inventory

Inventory Item ID	Item Last Status	Total Inventory Obsolete	Total On Hand Obsolete	Total Out Obsolete	Item Default Billing Logic Itype	Item Last Return Date
21767	Out On MCT58160	1	0	1	R	3/19/2024
WOT-RP040	Updated qty	1	0	1	R	5/1/2024

Work Order

Asset	Work Order	Status
WOT-RP040	56710	Open

I want to add a conditional column for CURRENT STATUS that contains a DAX stmt that says "if total out obsolete > 0 then return whichever is true

DT from delivery ticket detail with MAX(delivery date) where return date is blank

OR

Work Order from work order detail if status = Open

*It cannot be true for both*

ITEM	CURRENT STATUS
21767	on DT 58160
WOT-RP040	on Work Order 56710

[v-jialongy-msft · Accepted solution]

Hi @wotkara

Please try the following dax:

Create a Measure to Determine the Latest Delivery Ticket Detail: 

Latest DT Detail = 
VAR MaxDeliveryDate = CALCULATE(MAX('delivery_ticket_detail'[Delivery Date]), FILTER('delivery_ticket_detail', ISBLANK('delivery_ticket_detail'[Return Date])))
RETURN
IF(
    ISBLANK(MaxDeliveryDate),
    BLANK(),
    CONCATENATE("on DT ", CALCULATE(MAX('delivery_ticket_detail'[DT]), FILTER('delivery_ticket_detail', 'delivery_ticket_detail'[Delivery Date] = MaxDeliveryDate)))
)

Create a Measure for Open Work Order Status: 

Open Work Order Status = 
IF(
    CALCULATE(COUNTROWS('Work Order'),'Work Order'[Status] = "Open") > 0,
    CONCATENATE("on Work Order ", MAX('Work Order'[Work Order])),
    BLANK()
)

Create the Conditional Column for Current Status: 

Current Status = 
IF(
    [Total Out Obsolete] > 0,
    IF(
        NOT(ISBLANK([Latest DT Detail])),
        [Latest DT Detail],
        IF(
            NOT(ISBLANK([Open Work Order Status])),
            [Open Work Order Status],
            BLANK()
        )
    ),
    BLANK()
)

If the above formula doesn't solve your problem, please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny