Forum Discussion

Goodtogo's avatar
Goodtogo
Regular Visitor
3 years ago
Solved

DAX Conditional Lookup According to ID Column

Hi 

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

 

  • Given Table Name: [Order Table]
Order IDShipping  Status
301255Delivered
301255Not Started
301255Shipped
262022Delivered
262022Shipped
123333Delivered
123333Delivered
201012Not Started
201012Not 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 IDShipping  StatusNew_Status
301255DeliveredNot Started
301255Not StartedNot Started
301255ShippedNot Started
262022DeliveredShipped
262022ShippedShipped
123333DeliveredDelivered
123333DeliveredDelivered
201012Not StartedNot Started
201012Not StartedNot Started
DAX
power bi
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

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