Trying to make a row-based measure

[Anonymous]

Hi there, I'm trying to make a measure that does the following:

1. 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", "")
   
   
2.  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!

[v-alq-msft]

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.

[Anonymous]

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?

[v-alq-msft]

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.

[Anonymous]

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. 

[v-alq-msft · 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.

[Anonymous]

@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.

[v-alq-msft]

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.

[Greg_Deckler]

However, guessing at it, seems like you should do a final check for Is Engine 1 and Engine Life 1 similar to, if you are currently

RETURN
__Somevariable

Then change to for Is Engine 1:
RETURN
IF(MAX([Event ID]) = "ENGINE EXCHANGE",__SomeVariable,BLANK()
)

Similar for Engine Life 1:
RETURN
IF(MAX([Event ID]) = "ENGINE EXCHANGE" && MAX([Current Engine1] = "YES",BLANK(),__SomeVariable)

[Greg_Deckler]

@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.

[Anonymous]

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!

[Greg_Deckler]

Oh, that's probably because there is a syntax error in there somewhere. That tends to happen when I write DAX code without testing it first! 🙂

And I generally don't test it first because I don't feel like hand typing in a bunch of data!!

[Greg_Deckler]

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

[Greg_Deckler]

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", 
    ""
)