Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I would like to verify the number of Orders in a table for each Customer, and if all the Orders were shipped. If All the orders were shipped, then I want to say "All Orders Complete" is "Yes" for that Customer.
Example: Lets call this Orders Table -
Customer_ID | Order ID | Order Shipped | All Orders Complete |
A0001 | 1143095 | No | |
A0001 | 1080930 | Yes | No |
A0001 | 1139737 | Yes | No |
B0002 | 2584618 | No | |
B0002 | 2618961 | Yes | No |
C0003 | 3282832 | Yes | Yes |
C0003 | 3665548 | Yes | Yes |
C0003 | 3758698 | Yes | Yes |
Only C0003 is "Yes" for All Orders Complete because all three Orders for him were shipped. Then I want to present this data in this format in a visual (which I'll do)
Customer_ID | All Orders Complete |
A0001 | No |
B0002 | No |
C0003 | Yes |
The problem is, the Orders Table has specific SKU level information. I want to ignore the SKU level granularity and present the data at the Order level (and eventually at Customer level). So the granularity is Customer -> Order -> SKU.
This is what I have so far
All Orders Complete =
var temp = SUMMARIZE(OrdersTable, OrdersTable[Customer_ID], OrdersTable[Order ID], "Order Count", DISTINCTCOUNT(OrdersTable[OrderID]))"
return IF ( COUNTX(temp,[Order Count]) = DISTINCTCOUNT(OrdersTable[Order ID]), "Yes","No")
Any help is appreciated
Solved! Go to Solution.
Hi @RustyNails ,
You can try to use following measure formula if it suitable for your requirement:
Measure = IF ( CALCULATE ( DISTINCTCOUNT ( T1[Order ID] ) - CALCULATE ( DISTINCTCOUNT ( T1[Order ID] ), T1[Order Shipped] = "Yes" ), VALUES ( T1[Order ID] ) ) = 0, "Yes", "No" )
If above not help, please share some sample data for test.
Regards,
Xiaoxin Sheng
Hi @RustyNails ,
You can try to use following measure formula if it suitable for your requirement:
Measure = IF ( CALCULATE ( DISTINCTCOUNT ( T1[Order ID] ) - CALCULATE ( DISTINCTCOUNT ( T1[Order ID] ), T1[Order Shipped] = "Yes" ), VALUES ( T1[Order ID] ) ) = 0, "Yes", "No" )
If above not help, please share some sample data for test.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |