Order status based on date combinations

Anonymous — Thu, 08 Feb 2024 09:01:34 GMT

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

Re: Order status based on date combinations

lbendlin — Thu, 08 Feb 2024 23:02:19 GMT

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

topic Re: Order status based on date combinations in DAX Commands and Tips

Order status based on date combinations

Re: Order status based on date combinations