Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Trying to make a row-based measure

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

Need Help

v-alq-msft

6 years ago

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

Community Support

6 years ago

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

Not applicable

6 years ago

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?

Greg_Deckler
Community Champion
6 years ago
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
  Not applicable
  6 years ago
  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
    Community Champion
    6 years ago
    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
Community Champion
6 years ago
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)

v-alq-msft

Community Support

6 years ago

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

Not applicable

6 years ago

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

Community Support

6 years ago

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.

Forum Discussion

Trying to make a row-based measure

Related content

Count rows based on measure

Add duplicate row based on column

Measure to select rows based on dynamic conditions

summarize rows based on a row specific measure like rankx

Trying to use SELECTEDVALUE filter in a MOM measure