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 friends!
I have the following product sales facts table:
Order | Product | Delivery Date | cStatus |
1 | Mouse | 2019-12-01 | Delayed Delivery |
1 | Keyboard | 2020-01-01 | Delayed Delivery |
2 | CPU | 2019-12-20 | Delayed Delivery |
Using a count measure distinct by order number, I get the total orders delivered. In this example, the measure would return the total of 02 orders.
However, by plotting this measure on an axis line chart by date, the chart shows in the period the total of 03 orders delivered using the same previous measure because 1 order has deliveries on different dates.
I would like some help from you to know how to change this so that it shows in the graph the total of 02 orders, taking the largest date in the order that contains more than 01 delivery with different dates.
Can you help me?
Appreciate!
Below, the measure used:
@montilla Here is my approach.
First create a calculated column to get the max date per order.
Column = CALCULATE(MAX('FollowUp'[Delivery Date]),ALLEXCEPT('FollowUp','FollowUp'[Order]))
Modify your measure. i.e. use new column in relationship instead of delivery dates.
Measure = CALCULATE(DISTINCTCOUNT(FollowUp[Order]),USERELATIONSHIP('Calendar'[Date],FollowUp[Column]),FollowUp[cStatus]="Delayed Delivery")
If it helps accept as solution.
Good morning everyone!
Dear @Anonymous , your answer solved my problem. Thank you for your attention and support!
If you are not ordering too much, I would like to post a status column comparing whether within each order all items have a delivery date. If EVERYONE owns, throw in this column the value "Done". If there is at least 01 blank date, throw the value "Open". The column would only have the possibility of these 02 values.
Since I'm new to DAX, I still don't know how to get an array from an order's product list and compare if any date is blank. I have no idea how to do this considering order by order.
Could you help me with this problem?
Thanks again!
@montilla There are ways to do it. Here is the one approach
Column =
VAR _order = CALCULATE(COUNT('Table'[Order]),ALLEXCEPT('Table','Table'[Order]))
VAR _date = CALCULATE(COUNT('Table'[Delivery Date]),ALLEXCEPT('Table','Table'[Order]))
RETURN IF((_order-_date)>0,"Open","Done")
Up! Someone?
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |