Check if an order has previously been in order status

[metcala]

Hi all

Struggling to write a DAX column for this.

I have two tables:

Order table

Order ID	...
101	...
102	...

Order status history

Order ID	Date	Status
101	1/1/23	Order placed
101	1/1/23	On hold
101	1/1/23	Paid
101	1/1/23	Dispatched
102	1/1/23	Order placed
102	1/1/23	Paid
102	1/1/23	Dispatched

I am looking to create a column in the order table to highlight if the order has ever been placed on hold irrespective of current status.

Any help on this would be very much appreciated!!

[vicky_ · Accepted solution]

If there's a relationship between Order and the history, then you can try something like:

OnHold = COUNTROWS(FILTER(RELATEDTABLE('Order Status History'), 'Order Status History'[Status] = "On Hold")))

 this should be 0 if it's never been placed on hold, and a number >0 if it has.

[metcala]

Thanks for your help! @vicky_