Moving range calcultion in PowerBI service is slow

[mahenkj2]

Hi,

I have referred the below link to create moving range to create Individual-Moving range chart in Power BI:

https://community.powerbi.com/t5/Desktop/Moving-Range-DAX-subtract-previous-row-values-from-earlier/...

I created a .pbix file first with sample data and it works perfectly fine in the desktop version, but when I publish, visual just cant show the trends and it times out eventually. I dont see the way I can share the .pbix file, so submit codes used by me below:

below are the code:

Moving Range = 

VAR EarlierRecordID =
    CALCULATE (
        MAX ( Sheet1[RecordID] ),
        FILTER (
            ALLSELECTED ( Sheet1[RecordID]),
            Sheet1[RecordID] < SELECTEDVALUE ( Sheet1[RecordID] )
        )
    )

VAR EarlierMeasurementValue =
    CALCULATE ( SUM ( Sheet1[Measurement value] ), Sheet1[RecordID] = EarlierRecordID )
RETURN
    IF(ISBLANK(EarlierRecordID),BLANK(),   ABS ( EarlierMeasurementValue - SUM ( Sheet1[Measurement value] ) ))

AverageMovingRange = AverageX(ALLSELECTED(Sheet1[RecordID]),[Moving Range])

AverageClad = AVERAGEX(ALLSELECTED(Sheet1),Sheet1[Measurement value])

Sheet1 is the main table and I use above measures in the visuals. RecordID field is a calculated column and fetches unique ID with RELATED function. This RecordID is being used as x axis label in the said visual.

I suspect, either the DAX functions when being used in large table are causing the issue or the calculated column is the cause of problem. But sheet1 is imported table, so I also think that calculated column as such shoudl not be an issue in service, as it shoudl be stored value and while refreshing visual it should not hamper the performance.

I also submit sample data of sheet1  below:

RecordID    |    Product ID    |  Measurement value | Process Date

5739494522	Product ID 1009	125.196	08-01-2020 11:08:13
5739494241	Product ID 1008	124.888	08-01-2020 23:45:03
5739493962	Product ID 1007	124.888	09-01-2020 03:07:28
5739493942	Product ID 1006	125.03	09-01-2020 08:15:31
5739493621	Product ID 1005	125.03	10-01-2020 04:37:35
5739493398	Product ID 1004	124.894	11-01-2020 02:05:52
5739493208	Product ID 1003	124.894	11-01-2020 13:57:44
5739493018	Product ID 1002	124.869	12-01-2020 06:08:57
5739492828	Product ID 1001	124.869	12-01-2020 20:04:47
5739492806	Product ID 1000	124.714	13-01-2020 02:23:32
5738743706	Product ID 100	124.919	06-04-2021 04:20:28
5738582411	Product ID 10	125.032	21-05-2021 07:23:17
5738575049	Product ID 1	124.884	26-05-2021 18:56:11

Thanks.

[daxer-almighty · Accepted solution]

You can try these. I checked them on a sample model built with the data you provided. 'T' is the table's name and I abbreviated some of the fields' names. The assumption is that RecordID is a unique column. You'll notice that the [Moving Range] measure does not use CALCULATE. This is in order to avoid context transition which is a very costly operation. If you want to speed up the other measures, you'll have to get rid of CALCULATE from wherever possible and duplicate the code that's in [Moving Range] in other measures.

DEFINE 

MEASURE T[Moving Range] = 
IF( ISINSCOPE( T[RecordID] ),

    var CurrentRecordID = SELECTEDVALUE( T[RecordID] )
    var CurrentMeasurement = SELECTEDVALUE( T[Measurement] )
    var EarlierMeasurement =
        MAXX(
            TOPN(1,
                FILTER(
                    ALLSELECTED( T ),
                    T[RecordID] < CurrentRecordID
                ),
                T[RecordID],
                DESC
            ),
            T[Measurement]
         )
     var Result =
        if( not ISBLANK( EarlierMeasurement ),
            abs( EarlierMeasurement - CurrentMeasurement )
        )
     return
        Result
)

MEASURE T[AverageMovingRange] = 
AVERAGEX(
    ALLSELECTED( T ),
    [Moving Range]
)

MEASURE T[AverageClad] = 
AVERAGEX(
    ALLSELECTED( T ),
    T[Measurement]
)

[mahenkj2]

@daxer-almightyThanks. If RecordID is not unique, what should I do in that case?

[daxer-almighty]

Then you have a data quality problem and should clean the data.

[mahenkj2]

RecordID is unique in my case due to slicers being applied at the time of report viewing. I asked to understand what it can cause and then to make proper proofing for any such effects.

Thanks a lot for your support. I publoshed the report and it works well.

[daxer-almighty]

If RecordID were non-unique, then the variable EarlierMeasurement in [Moving Range] would obtain the measurement of the highest value among all the measurements with the same RecordID, which could or could not be what you want. However, RecordID is a name that strongly suggests it is/should be unique.

[mahenkj2]

Please find .pbix file here:

https://drive.google.com/file/d/1PDgqHughOinQOll2C7L_GCYdDxiVUWdq/view?usp=sharing 

Please note that this is not actual file, trimmed version and so RecordID column is not as calculated column. This is the max I can share on community forum.

[daxer-almighty]

"I dont see the way I can share the .pbix file,"

Just put the file on some shared drive - Google Drive, Dropbox, OneDrive... - and paste a link to the file in here. This is how you share files.