topic Re: Dynamic Row Calculation in DAX Commands and Tips

Dynamic Row Calculation

erichock — Fri, 29 Nov 2024 08:38:09 GMT

Hi,

I am having a brain block, trying to get this to work. I am trying to get the difference between each set of 'vehicle plate no', current mileage (based on rank).

Entries of the same 'vehicle plate no' will increase each month. Therefore the "Gen Difference" DAX needs to be dynamic.

This is an example of what I am trying to achieve.

DAX(Measure)

Rank = RANKX(FILTER('mMjIms8','mMjIms8'[Vehicle Plate No]=EARLIER('mMjIms8'[Vehicle Plate No])),'mMjIms8'[Created At],,DESC,Dense)
Gen Difference =
VAR _RANK1GenHours =
CALCULATE (
SUM ( mMjIms8[Current Mileage]),
FILTER (
ALL ( mMjIms8 ),
'mMjIms8'[Vehicle Plate No] = MAX ( 'mMjIms8'[Vehicle Plate No])
&& 'mMjIms8'[Rank] = 1
)
)
VAR _RANK2GenHours =
CALCULATE (
SUM ( mMjIms8[Current Mileage]),
FILTER (
ALL ( mMjIms8 ),
'mMjIms8'[Vehicle Plate No] = MAX ( 'mMjIms8'[Vehicle Plate No])
&& 'mMjIms8'[Rank] = 2
)
)
RETURN
IF (
SUM ( 'mMjIms8'[Rank] ) = 1,
IF ([_RANK1GenHours] = ABS( _RANK1GenHours - _RANK2GenHours ),Blank() ,ABS( _RANK1GenHours - _RANK2GenHours )),
BLANK ()
)

Rank's DAX is working perfectly fine but I couldn't get "VAR _RANK2GenHours" to automatically deduct 1 on each row.

I have tried "&& 'mMjIms8'[Rank] = 'mMjIms8'[Rank]-1, but this results in the entire column being blank.

What am I missing over here? 🙏
(Your help is much appreciated over here 👍)

Re: Dynamic Row Calculation

johnt75 — Fri, 29 Nov 2024 09:48:13 GMT

Try

Gen Difference = VAR FirstEntry = INDEX ( 1, ALLSELECTED ( mMjIms8 ), ORDERBY ( mMjIms8[Created At], DESC ), PARTITIONBY ( mMjIms8[Vehicle Plate No] ), MATCHBY ( mMjIms8[Vehicle Plate No] ) ) VAR SecondEntry = INDEX ( 2, ALLSELECTED ( mMjIms8 ), ORDERBY ( mMjIms8[Created At], DESC ), PARTITIONBY ( mMjIms8[Vehicle Plate No] ), MATCHBY ( mMjIms8[Vehicle Plate No] ) ) VAR FirstMileage = SELECTCOLUMNS ( FirstEntry, mMjIms8[Current Mileage] ) VAR SecondMileage = SELECTCOLUMNS ( FirstEntry, mMjIms8[Current Mileage] ) VAR Result = FirstMileage - SecondMileage RETURN Result

Re: Dynamic Row Calculation

Jihwan_Kim — Sat, 30 Nov 2024 05:30:38 GMT

Hi,

Please check the below picture and the attached pbix file.

OFFSET function (DAX) - DAX | Microsoft Learn

expected result measure: = VAR _currentmileage = SUM ( mMjIms8[current_mileage] ) VAR _previous = CALCULATE ( SUM ( mMjIms8[current_mileage] ), OFFSET ( -1, SUMMARIZE ( ALL ( mMjIms8 ), mMjIms8[vehicle_plate_no], mMjIms8[date], mMjIms8[current_mileage] ), ORDERBY ( mMjIms8[date], ASC ), , PARTITIONBY ( mMjIms8[vehicle_plate_no] ) ) ) RETURN IF ( NOT ISBLANK ( _previous ) && HASONEVALUE ( mMjIms8[vehicle_plate_no] ), _currentmileage - _previous )

Re: Dynamic Row Calculation

erichock — Mon, 02 Dec 2024 08:05:08 GMT

Thanks Kim,

I got this to work with your solution. 🤝

Re: Dynamic Row Calculation

erichock — Mon, 02 Dec 2024 08:06:13 GMT

Thanks Kim,

I got it to work with your help 🙏