Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |