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 there, I'm trying to make a measure that does the following:
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!
Solved! Go to 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.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario.
Table:
You may create three measures as below.
Current Engine =
var _date = SELECTEDVALUE('Table'[Date])
var _equipmentid = SELECTEDVALUE('Table'[Equipment ID])
var _eventid = SELECTEDVALUE('Table'[Event ID])
var _val =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"
)
)
return
IF(
ISFILTERED('Table'[Equipment ID]),
IF(
_val = _date,
"YES"
)
)
Is Engine =
var _date = SELECTEDVALUE('Table'[Date])
var _equipmentid = SELECTEDVALUE('Table'[Equipment ID])
var _eventid = SELECTEDVALUE('Table'[Event ID])
var _enddate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"
)
)
var _startdate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date]<_enddate
)
)
return
IF(
ISFILTERED('Table'[Equipment ID]),
IF(
_date>=_startdate&&
_date<=_enddate,
"YES"
)
)
Engine Life =
var _date = SELECTEDVALUE('Table'[Date])
var _equipmentid = SELECTEDVALUE('Table'[Equipment ID])
var _eventid = SELECTEDVALUE('Table'[Event ID])
var _enddate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"
)
)
var _startdate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date]<_enddate
)
)
var _starthourreading =
CALCULATE(
SUM('Table'[Hour Reading]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date] = _startdate
)
)
var _endhourreading =
CALCULATE(
SUM('Table'[Hour Reading]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date] = _enddate
)
)
return
IF(
_date>=_startdate&&
_date<=_enddate,
_endhourreading-_starthourreading
)
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.
Thanks, I have it working on my end, would you be able to show me what the measures would look like if I wanted the table to look like this?
Hi, @Anonymous
You may try measures as below.
Current Engine =
var _date = SELECTEDVALUE('Table'[Date])
var _equipmentid = SELECTEDVALUE('Table'[Equipment ID])
var _eventid = SELECTEDVALUE('Table'[Event ID])
var _val =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"
)
)
return
IF(
ISFILTERED('Table'[Equipment ID]),
IF(
_val = _date,
"YES"
)
)
Is Engine =
var _date = SELECTEDVALUE('Table'[Date])
var _equipmentid = SELECTEDVALUE('Table'[Equipment ID])
var _eventid = SELECTEDVALUE('Table'[Event ID])
var _enddate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"
)
)
var _startdate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date]<_enddate
)
)
return
IF(
ISFILTERED('Table'[Equipment ID]),
IF(
_date=_startdate||
_date=_enddate,
"YES"
)
)
Engine Life =
var _date = SELECTEDVALUE('Table'[Date])
var _equipmentid = SELECTEDVALUE('Table'[Equipment ID])
var _eventid = SELECTEDVALUE('Table'[Event ID])
var _enddate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"
)
)
var _startdate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date]<_enddate
)
)
var _starthourreading =
CALCULATE(
SUM('Table'[Hour Reading]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date] = _startdate
)
)
var _endhourreading =
CALCULATE(
SUM('Table'[Hour Reading]),
FILTER(
ALL('Table'),
'Table'[Equipment ID] = _equipmentid&&
'Table'[Event ID] = "ENGINE EXCHANGE"&&
'Table'[Date] = _enddate
)
)
return
IF(
_date>=_startdate&&
_date<_enddate,
_endhourreading-_starthourreading
)
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.
Thanks for the help! I'm having some issues with getting the measures to work in SSAS, but I think that's for me to figure out 🙂
Would you be able to help me out in writing these cumulative cost/age measures? Not sure if they're hard to write.
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.
@v-alq-msft
Great, thanks! With the other measures you had helped me out with, would they vary in SSAS? e.g. Is Engine, Engine Life, Current Engine, etc.
Hi, @Anonymous
I have tried to use SSAS with live connection. It appears normal.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous - Can you post the code for your current measures? Would be easier for me to be accurate about how to modify them to achieve what you want.
I'm using the three measures posted by v-alq-msft, I tried using the measure that you posted as well, however for some reason it gives me a "Unexpected expression "RETURN".
Thanks!
Engine life will be something like:
Engine Life Measure =
VAR __Truck = MAX('Table'[Equipment ID])
VAR __CurrentDate = MAX('Table'[Date])
VAR __LastEngineDate =
MAXX(
FILTER(
ALL('Table'),
[Equipment ID] = __Truck &&
[Date] < __CurrentDate &&
[Is Engine] = "YES"
),
[Date]
)
VAR __Hours =
IF(MAX([Is Engine]) = "YES",
MAX([Hours Reading]),
SUMX(
FILTER(
ALL('Table'),
[Equipment ID] = __Truck &&
[Date] <= __CurrentDate &&
[Date] >= __LastEngineDate
),
[Hour Reading]
)
RETURN
__Hours
I think:
Current Engine =
VAR __Truck = MAX('Table'[Equipment ID])
RETURN
IF (
SUM('Table'[Hour Reading]) =
CALCULATE (
MAX('Table'[Hour Reading]),
FILTER ( ALLSELECTED('Table'), [Equipment ID] = __Truck)
),
'Table'[Hour Reading]
)
"Yes",
""
)
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |