Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi PowerBI Guru's
I am hoping someone could help me please, as it seems simple enough but I am unable to build this calculated column.
Below is a screenshot of my data fields and here is the logic I am trying to build.
Logic as follows:
Begin...
if ALL dates are blank then = "Order not yet booked"
if [Booking Date] is not blank but [Pick Up Date] and [Departed Date] and [Arrived Date] and [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order Booked but not picked up"
if [Pick Up Date] is not blank but [Departed Date] and [Arrived Date] and [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order Picked Up but not departed"
if [Departed Date] is not blank but [Arrived Date] and [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order Departed but not arrived"
if [Arrived Date] is not blank but [Clearance Start Date] and [Clearance End Date] and [Delivery Date] is blank then "Order arrived but not started clearance"
if [Clearance Start Date] is not blank but [Clearance End Date] and [Delivery Date] is blank then "Order started clearance but clearance not yet completed"
if [Clearance End Date] is not blank but [Delivery Date] is blank then "Clearance completed but not delivered"
if [Delivery Date] is not blank then "Order delivered"
End...
Solved! Go to Solution.
Hi @Anno2019 ,
Please use the measure below:
Measure = IF ( NOT ( ISBLANK ( MAX ( Table1[Booked Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date]))&&ISBLANK(MAX([Pick Up Date])), "Order Booked but not picked up", IF ( NOT ( ISBLANK ( MAX ( Table1[Pick Up Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date])), "Order Picked Up but not departed", IF ( NOT ( ISBLANK ( MAX ( Table1[Departed Date] ) ) ) &&ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order Departed but not arrived", IF ( NOT ( ISBLANK ( MAX ( Table1[Arrived Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order arrived but not started clearance", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance Start Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Delivery Date])), "Order started clearance but clearance not yet completed", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance End Date] ) ) ) && MAX( Table1[Delivery Date] ) = BLANK (), "Clearance completed but not delivered", IF ( NOT ( ISBLANK ( MAX ( Table1[Delivery Date] ) ) ), "Order delivered", "Order not yet booked" ) ) ) ) ) ) )
Best regards,
Dina Ye
Hi @Anno2019 ,
Please use the measure below:
Measure = IF ( NOT ( ISBLANK ( MAX ( Table1[Booked Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date]))&&ISBLANK(MAX([Pick Up Date])), "Order Booked but not picked up", IF ( NOT ( ISBLANK ( MAX ( Table1[Pick Up Date] ) ) ) && ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date]))&&ISBLANK(MAX([Departed Date])), "Order Picked Up but not departed", IF ( NOT ( ISBLANK ( MAX ( Table1[Departed Date] ) ) ) &&ISBLANK(MAX([Arrived Date]))&&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order Departed but not arrived", IF ( NOT ( ISBLANK ( MAX ( Table1[Arrived Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Clearance Start Date]))&&ISBLANK(MAX([Delivery Date])), "Order arrived but not started clearance", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance Start Date] ) ) ) &&ISBLANK(MAX([Clearance End Date]))&&ISBLANK(MAX([Delivery Date])), "Order started clearance but clearance not yet completed", IF ( NOT ( ISBLANK ( MAX ( Table1[Clearance End Date] ) ) ) && MAX( Table1[Delivery Date] ) = BLANK (), "Clearance completed but not delivered", IF ( NOT ( ISBLANK ( MAX ( Table1[Delivery Date] ) ) ), "Order delivered", "Order not yet booked" ) ) ) ) ) ) )
Best regards,
Dina Ye
Hey Dina
Just a quick question.
How would I be able to use this as a filter. Currently it works as a calculated measure, but it does not work as a dimension...any thoughts?
Worked like magic
Thank you so much!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |