cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Trying to make a row-based measure

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

needs.PNG

  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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Trying to make a row-based measure

Hi, @arjuncheema 

 

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:

c1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
Highlighted
Super User IV
Super User IV

Re: Trying to make a row-based measure

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Trying to make a row-based measure

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Trying to make a row-based measure

Hi, @arjuncheema 

 

Based on your description, I created data to reproduce your scenario.

Table:

e1.png

 

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:

e2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Regular Visitor

Re: Trying to make a row-based measure

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?measures.PNG


Highlighted
Super User IV
Super User IV

Re: Trying to make a row-based measure

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Regular Visitor

Re: Trying to make a row-based measure

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!

Highlighted
Super User IV
Super User IV

Re: Trying to make a row-based measure

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)

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Trying to make a row-based measure

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Trying to make a row-based measure

Hi, @arjuncheema 

 

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:

a1.png

 

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

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors