Hi there, I'm trying to make a measure that does the following:
- Gives a "Yes" value to rows of the most recent engine exchange per truck. I've currently written a measure that only gives me a "Yes" value for the entire column, because I haven't figured out how to run the measure on each truck, instead it evaluates all of them. I've highlighted where an additional "Yes" should be in blue, whereas it only shows the "Yes" at the bottom. Here's the DAX: Current Engine =IF (SUM('Table'[Hour Reading]) =CALCULATE (MAX('Table'[Hour Reading]),FILTER ( ALLSELECTED('Table'), ('Table'[Hour Reading]))),"Yes", "")
- The "engine life" column should be a calculation of the hour reading from the most recent engine exchange minus the previous one. So for Truck 2, the Engine Life values in the 0, 179, 544, 1490, and 1647 rows should be 1647, since that was the life of that specific engine. Then it resets after 1647 since that's a newly installed engine after.
Sorry if this doesn't make sense, I tried to explain it as best as I could. Let me know if anything needs to be clarified.
Thanks!
1 ACCEPTED SOLUTION
Hi, @Anonymous
You may try the following measure in SSAS.
Cumulative Age =
var _date = SELECTEDVALUE(Table1[Date])
var _equipmentid = SELECTEDVALUE(Table1[Equipment ID])
var _eventid = SELECTEDVALUE(Table1[Event ID])
var _maxdate =
CALCULATE(
MAX(Table1[Date]),
FILTER(
ALL(Table1),
Table1[Equipment ID] = _equipmentid
)
)
return
IF(
_eventid = "ENGINE EXCHANGE",
0,
IF(
_date = _maxdate
,
SELECTEDVALUE(Table1[Hour Reading])-
CALCULATE(
SUM(Table1[Hour Reading]),
FILTER(
ALL(Table1),
Table1[Equipment ID] = _equipmentid&&
Table1[Event ID] = "ENGINE EXCHANGE"
)
),
SELECTEDVALUE(Table1[Hour Reading])
)
)
Cumulative Cost =
var _date = SELECTEDVALUE(Table1[Date])
var _equipmentid = SELECTEDVALUE(Table1[Equipment ID])
var _eventid = SELECTEDVALUE(Table1[Event ID])
var _lastdate =
CALCULATE(
MAX(Table1[Date]),
FILTER(
ALL(Table1),
Table1[Equipment ID] = _equipmentid&&
Table1[Event ID] = "ENGINE EXCHANGE"&&
Table1[Date]<_date
)
)
return
IF(
_eventid = "ENGINE EXCHANGE",
SELECTEDVALUE(Table1[Cost]),
CALCULATE(
SUM(Table1[Cost]),
FILTER(
ALL(Table1),
Table1[Equipment ID] = _equipmentid&&
Table1[Date]>=_lastdate&&
Table1[Date]<=_date
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
