Optimising DAX Measures

[JB2020]

I'm looking to optimise the following measures with the aim of creating a pivot table containing 'MyValue' and a grand average 2 point moving range centre line (the AverageMovingRange measure below).

Because various other fields can be added as filters, as well as ID being grouped by fields, this means that I can not use a calculated column as it needs to be dynamic. Below is an example of an output I am looking to achieve, where Category and ID are 'Rows' in a pivot table and my measures and the [sum of] MyValues field are 'Values'.

It's also probably worth mentioning that the ID field is unique in each row of the 'Data' table.

The two measures below produce the desired output when the table size is small (a few hundred rows). However, with the full table containing tens of thousands of rows it seems to take an eternity, even when filters are applied. Any help on how to optimize these measures is greatly appreciated.
Please let me know if you need me to provide any more info, thanks in advance!

Current Measures:

This measure looks for the next largest ID than the one in the current row context, retrieves the value of the 'MyValue' field for that row, then takes the absolute value of that, minus the current MyValue for that row.

Moving Range = 

VAR RowID = LASTNONBLANK('Data'[ID],1)


VAR LastID = CALCULATE(
    MAX('Data'[ID]),
    ALL('Data'[ID]),
    'Data'[ID]<RowID)


RETURN IF(ISBLANK(LastID),BLANK(),
        ABS(SUM([MyValue]) -
            LOOKUPVALUE('Data'
                [MyValue],
                'Data'[ID],
                LastID)
            )
    )

This measure takes the average of [Moving range] for each row, keeping all filters besides ID

 AverageMovingRange = AVERAGEX(ALL('Data'[ID]),[Moving Range])

 

[SharmaAntriksh]

I know it is a bit late but hope this helps! You code is slow because you are using a lot of functions that put load on the Formula Engine of Analysis Services.

Try this

AverageMovingRange =
VAR DataTable_ =
    CALCULATETABLE (
        Data,
        ALLEXCEPT ( Data, Data[Category] )
    )
VAR Result =
    AVERAGEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                DataTable_,
                "@Previous Value",
                    VAR CurrentCategory = Data[Category]
                    VAR CurrentID = Data[ID]
                    VAR RowsForCurrentCategory = FILTER ( DataTable_, Data[Category] = CurrentCategory  )
                    VAR PreviousID = MAXX ( FILTER ( RowsForCurrentCategory, Data[ID] < CurrentID ), Data[ID]  )
                    VAR ValueAtPreviousID = SUMX ( FILTER ( RowsForCurrentCategory, Data[ID] = PreviousID ), Data[MyValue] )
                    RETURN ValueAtPreviousID
            ),
            "@MovingRange", IF ( NOT ISBLANK ( [@Previous Value] ), Data[MyValue] - [@Previous Value] )
        ),
        ABS ( [@MovingRange] )
    )
RETURN Result

 

[JB2020]

I know it is a bit late but hope this helps! You code is slow because you are using a lot of functions that put load on the Formula Engine of Analysis Services.

Try this

AverageMovingRange =
VAR DataTable_ =
    CALCULATETABLE (
        Data,
        ALLEXCEPT ( Data, Data[Category] )
    )
VAR Result =
    AVERAGEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                DataTable_,
                "@Previous Value",
                    VAR CurrentCategory = Data[Category]
                    VAR CurrentID = Data[ID]
                    VAR RowsForCurrentCategory = FILTER ( DataTable_, Data[Category] = CurrentCategory  )
                    VAR PreviousID = MAXX ( FILTER ( RowsForCurrentCategory, Data[ID] < CurrentID ), Data[ID]  )
                    VAR ValueAtPreviousID = SUMX ( FILTER ( RowsForCurrentCategory, Data[ID] = PreviousID ), Data[MyValue] )
                    RETURN ValueAtPreviousID
            ),
            "@MovingRange", IF ( NOT ISBLANK ( [@Previous Value] ), Data[MyValue] - [@Previous Value] )
        ),
        ABS ( [@MovingRange] )
    )
RETURN Result

Works perfectly, thank you!