Order status based on date combinations

[Anonymous]

Hello,

My question is related to showing the Status in a large database of different orders based on: same order number and Received / Shipped combinations?

I have included a sample below. Basically there can be dozens of same order numbers, with different quantities, but different Received / Shipped date combinations (Received and Shipped, Partially shipped, etc.)

The thing that throws me off is the "same order#", how could I group them, while still having different data on a row by row basis? What would be the best choice here?

Thank you!

Order#	Quantity	Received date	Shipped date	Status
12345678	12	29/01/2024	30/01/2024	Received and Shipped
12345678	2	29/01/2024	30/01/2024	Received and Shipped
12345678	34	29/01/2024	30/01/2024	Received and Shipped
12345678	21	29/01/2024	30/01/2024	Received and Shipped
12345678	11	29/01/2024	30/01/2024	Received and Shipped
12345678	6	29/01/2024	30/01/2024	Received and Shipped
87654321	4	29/01/2024		Only Received
87654321	37	29/01/2024		Only Received
87654321	23	29/01/2024		Only Received
87654321	2	29/01/2024		Only Received
54321678	45			Ordered
54321678	32			Ordered
54321678	11			Ordered
54321678	7			Ordered
67854321	56	29/01/2024		Partially received
67854321	3			Partially received
67854321	43	29/01/2024		Partially received
67854321	21	29/01/2024		Partially received
67854321	9			Partially received

[lbendlin · Accepted solution]

Note that this is a calculated column, tagging each order line.

Status = 
var o = [Order#]
var a = filter(all('Table'),[Order#]=o)
RETURN switch(TRUE(),
countrows(a)=sumx(a,if(ISBLANK([Received date]),0,1)) && countrows(a)=sumx(a,if(ISBLANK([Shipped date]),0,1)),"Received and Shipped",
countrows(a)=sumx(a,if(ISBLANK([Received date]),0,1)),"Only Received",
sumx(a,if(ISBLANK([Received date]),0,1))>0,"Partially Received",
"Ordered")