cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Order status based on date combinations

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

1 ACCEPTED SOLUTION
Super User

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",
"Ordered")``````

Super User

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",
"Ordered")``````

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors