Forum Discussion

OpenMike13's avatar
OpenMike13
Frequent Visitor
4 years ago
Solved

Creating Custom Column "status" based on multiple columns.

Hello 

 

Im having issues trying to find a way to create a custom Status column based on multiple other columns within the same table. I am looking to provide a status on a class shipments based on where its at in our shipping process. 

here is how the data is set up in our database 

 

CourseOrder ReceivedDate shippedDate of delivery to courseDate Scheduled for returnDate Returned
XX3/2/20223/5/20223/8/20223/20/202222-Mar
TT4/2/20224/5/20224/8/20224/20/20224/22/2022
AA4/15/20224/18/20224/20/2022  
DD4/18/20224/25/2022   
CC4/27/2022    

 

Here is what the status column should look like

 

CourseOrder ReceivedDate shippedDate of delivery to courseDate Scheduled for returnDate ReturnedStatus
XX3/2/20223/5/20223/8/20223/20/202222-MarCompleted/Returned
TT4/2/20224/5/20224/8/20224/20/20224/22/2022Completed/Returned
AA4/15/20224/18/20224/20/2022  Delivered to Course
DD4/18/20224/25/2022   Shipped to Course
CC4/27/2022    Order Received

 

 

Any help is appreciated! thank you 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi OpenMike13 

     

    Download example PBIX file

     

    This will create your column

     

    Status = 

SWITCH(

    TRUE(),

    NOT ISBLANK([Date Returned]), "Completed/Returned",

    NOT ISBLANK([Date of delivery to course]), "Delivered to Course",

    NOT ISBLANK([Date shipped]), "Shipped to Course",

    "Order Received"
)

     

     

     

    Note that you don't have any status for something that is scheduled for return, and not yet returned.  But you just need to add another line to the code above for that.

     

    Regards

     

    Phil

1 Reply

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi OpenMike13 

     

    Download example PBIX file

     

    This will create your column

     

    Status = 

SWITCH(

    TRUE(),

    NOT ISBLANK([Date Returned]), "Completed/Returned",

    NOT ISBLANK([Date of delivery to course]), "Delivered to Course",

    NOT ISBLANK([Date shipped]), "Shipped to Course",

    "Order Received"
)

     

     

     

    Note that you don't have any status for something that is scheduled for return, and not yet returned.  But you just need to add another line to the code above for that.

     

    Regards

     

    Phil