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.
Hi all,
I'm struggling with constructing a DAX-formula and could really use some help.
I have a table with sales data from (multiple) coffee vending machines that looks like:
Date | Machine | Product |
15-4-2022 | A | ERROR |
18-4-2022 | A | Cappuccino |
23-4-2022 | A | Cappuccino |
24-4-2022 | A | ERROR |
26-4-2022 | A | Espresso |
etc.. | etc.. | etc.. |
I have a table with Operating Hours per date and vending machine that looks like*:
Date | Machine | Hours Operated |
1-4-2022 | A | 2000 |
24-4-2022 | A | 2500 |
24-4-2022 | A | 2510 |
1-5-2022 | A | 2560 |
21-5-2022 | A | 2990 |
etc.. | etc.. | etc.. |
*Note that there are long periods without data, and also periods with multiple data points on one date.
Finally, I have a table with the vending machines:
Machine | Model |
A | ZB-2.0 |
B | ZB-2.2 |
C | ZB-2.2 |
The end result that I want is this:
Machine | Model | Last Error Date | Hours Operated until Error |
A | ZB-2.0 | 24-4-2022 | 2510 |
B | ZB-2.2 | ... | ... |
C | ZB-2.2 | ... | ... |
Where the DAX-formula for the 'Last Error Date'-measure in the Sales-table is written as:
Last Error Date =
MAXX(
TOPN( 1, FILTER (
'Sales',
'Sales'[Product] in {"ERROR"}
), 'Sales'[Date], DESC),'Sales'[Date])
For the corresponding 'Hours Operated until Error', I want to retrieve the value from the 'Operating Hours'-table, for the date that is on or before the date of the Last Error Date. If there are multiple values equally 'close to' the Last Error Date, then I want to pick the highest value.
In this case, the Last Error Date was 24-4, which means the two 'Hours Operated'-values of 2500 and 2510 qualify equally as 'closest' to the Last Error Date, but 2510 is higher so this value is returned.
How do I write the DAX-formula for this 'Hours Operated until Error'-measure?
Your help is very much appreciated! 🙂
Solved! Go to Solution.
Hi @zudar ,
I have created a simple sample, please refer to it to see if it helps you.
Create two measures.
Measure =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
ALL ( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = SELECTEDVALUE ( machines3[Machine] )
)
)
AMX_VALUE =
VAR _1 =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
ALL ( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = SELECTEDVALUE ( machines3[Machine] )
)
)
RETURN
MAXX (
FILTER ( ALL ( 'operating 2' ), 'operating 2'[Date] = _1 ),
'operating 2'[Hours Operated]
)
Or two columns.
Column =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = EARLIER ( machines3[Machine] )
)
)
Column AMX_VALUE =
VAR _1 =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
ALL ( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = EARLIER ( machines3[Machine] )
)
)
RETURN
MAXX (
FILTER ( 'operating 2', 'operating 2'[Date] = _1 ),
'operating 2'[Hours Operated]
)
If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @zudar ,
I have created a simple sample, please refer to it to see if it helps you.
Create two measures.
Measure =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
ALL ( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = SELECTEDVALUE ( machines3[Machine] )
)
)
AMX_VALUE =
VAR _1 =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
ALL ( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = SELECTEDVALUE ( machines3[Machine] )
)
)
RETURN
MAXX (
FILTER ( ALL ( 'operating 2' ), 'operating 2'[Date] = _1 ),
'operating 2'[Hours Operated]
)
Or two columns.
Column =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = EARLIER ( machines3[Machine] )
)
)
Column AMX_VALUE =
VAR _1 =
CALCULATE (
MAX ( 'sales data'[Date] ),
FILTER (
ALL ( 'sales data' ),
'sales data'[Product] = "ERROR"
&& 'sales data'[Machine] = EARLIER ( machines3[Machine] )
)
)
RETURN
MAXX (
FILTER ( 'operating 2', 'operating 2'[Date] = _1 ),
'operating 2'[Hours Operated]
)
If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |