DAX Conditional Lookup According to ID Column

[Goodtogo]

Hi 

I have the data below and looking for DAX to create New_Status column.

• Given Table Name: [Order Table]

Order ID	Shipping  Status
301255	Delivered
301255	Not Started
301255	Shipped
262022	Delivered
262022	Shipped
123333	Delivered
123333	Delivered
201012	Not Started
201012	Not Started

Logic:-

When one order id has a combination of shipping Status:  "Deliverd","Not Started" and "Shipped" THEN " Not Started"

When one order id has a combination of shipping Status:  "Deliverd" and "Shipped" THEN " Shipped"

When one order id has a single  shipping Status:  "Deliverd" THEN " Delivered"

When one order id has a single  shipping Status:  "Not Started" THEN " Not Started"

• Expected result should be based on the above logic giving us results indicated below:

Order ID	Shipping  Status	New_Status
301255	Delivered	Not Started
301255	Not Started	Not Started
301255	Shipped	Not Started
262022	Delivered	Shipped
262022	Shipped	Shipped
123333	Delivered	Delivered
123333	Delivered	Delivered
201012	Not Started	Not Started
201012	Not Started	Not Started

[Jihwan_Kim · Accepted solution]

Hi,

Based on the above condition, I tried to create a calculated column like below.

Please check the below picture and the attached pbix file.

New_Status CC =
VAR _referencecolumn =
    SUMMARIZE (
        FILTER ( Data, Data[Order ID] = EARLIER ( Data[Order ID] ) ),
        Data[Shipping  Status]
    )
RETURN
    SWITCH (
        TRUE (),
        "Delivered"
            IN _referencecolumn
            && "Not Started"
            IN _referencecolumn
            && "Shipped" IN _referencecolumn, "Not Started",
        "Delivered"
            IN _referencecolumn
            && "Shipped" IN _referencecolumn, "Shipped",
        "Delivered" IN _referencecolumn, "Delivered",
        "Not Started" IN _referencecolumn, "Not Started"
    )

[Goodtogo]

HI @Jihwan_Kim 

Thanks for the solution it works perfectly.