Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
EM033
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#QuantityReceived dateShipped dateStatus
12345678 1229/01/202430/01/2024Received and Shipped
12345678 229/01/202430/01/2024Received and Shipped
12345678 3429/01/202430/01/2024Received and Shipped
12345678 2129/01/202430/01/2024Received and Shipped
12345678 1129/01/202430/01/2024Received and Shipped
12345678 629/01/202430/01/2024Received and Shipped
87654321 429/01/2024 Only Received
87654321 3729/01/2024 Only Received
87654321 2329/01/2024 Only Received
87654321 229/01/2024 Only Received
54321678 45  Ordered
54321678 32  Ordered
54321678 11  Ordered
54321678 7  Ordered
67854321 5629/01/2024 Partially received
67854321 3  Partially received
67854321 4329/01/2024 Partially received
67854321 2129/01/2024 Partially received
67854321 9  Partially received

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

lbendlin_0-1707433266970.png

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

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

lbendlin_0-1707433266970.png

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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