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.
I have a table with multiple date fields (Order Date, Ship Date, Delivery Date). I need to report on counts of sales, shipment and delivery for each month where either sale, shipment or delivery was made.
Here is an example of mockup source data
Sales_Order_Num | Product | Order_Date | Ship_Date | Delivery_Date |
1 | P1 | 8/19/2020 | 8/29/2020 | 9/3/2020 |
2 | P1 | 8/24/2020 | 9/3/2020 | 9/13/2020 |
3 | P1 | 9/3/2020 | 9/8/2020 | 9/19/2020 |
I need to display counts like this
Month | Order Count | Shipment Count | Delivery Count |
August | 2 | 1 | 0 |
September | 1 | 2 | 3 |
I have a date table and created one active (on Order date) and 2 indirect (ship and delivery date) relations.
I created measures for shipment and Delivery counts using USERELATIONSHIP like below
Month is from Calendar table
Month | Order Count | Shipment Count | Delivery Count |
August | 2 | 2 | 2 |
September | 1 | 1 | 1 |
How do I get the shipment and delivery counts displayed correctly?
Solved! Go to Solution.
Hi @prekapRev -
Force your relationships between Cal_Date and Sales to be 1-to-many (Cal_Date on the 1 side) and it should work for you.
Hope this helps
David
Hi,
Share the link from where i can download your PBI file.
Hi @prekapRev - I cannot reproduce your results. I get your expected output when I put your code in (although the DAX says "Sales Order No", while the data says "Sales Order Num", but I assume that was just an error in typing).
Order Count = CALCULATE(COUNT(Sales[Sales_Order_Num]))
Ship_Count = CALCULATE(count(Sales[Sales_Order_Num]),
USERELATIONSHIP(DateTab[Date], Sales[Ship_Date]))
Delivery_Count = CALCULATE(count(Sales[Sales_Order_Num]),
USERELATIONSHIP(DateTab[Date], Sales[Delivery_Date]))
Perhaps you left something out of the explanation that makes the situation more complicated?
David
@dedelman_clng I want to attach my pbix file but I don't know how to attach any files here. I have done it exactly like you are showing in your solution but my result is different. not sure why.
Put it on a Dropbox, google drive or OneDrive and put the link into a post.
Here is the link to my report
https://drive.google.com/file/d/1CTl7i0qiUvqYu0I-PuQPKkld19XVXxlm/view?usp=sharing
Hi @prekapRev -
Force your relationships between Cal_Date and Sales to be 1-to-many (Cal_Date on the 1 side) and it should work for you.
Hope this helps
David
Changing cardinality fixed the problem. Thanks! I wonder though that why should that be the problem 🤔
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |