topic Re: Moving range calcultion in PowerBI service is slow in DAX Commands and Tips

Moving range calcultion in PowerBI service is slow

mahenkj2 — Sun, 06 Jun 2021 12:52:26 GMT

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/m-p/362905

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.

Re: Moving range calcultion in PowerBI service is slow

daxer-almighty — Sun, 06 Jun 2021 14:39:15 GMT

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

Re: Moving range calcultion in PowerBI service is slow

mahenkj2 — Sun, 06 Jun 2021 15:26:57 GMT

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.

Re: Moving range calcultion in PowerBI service is slow

daxer-almighty — Sun, 06 Jun 2021 15:46:55 GMT

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] )

Re: Moving range calcultion in PowerBI service is slow

mahenkj2 — Sun, 06 Jun 2021 17:30:52 GMT

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

Re: Moving range calcultion in PowerBI service is slow

daxer-almighty — Mon, 07 Jun 2021 08:47:32 GMT

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

Re: Moving range calcultion in PowerBI service is slow

mahenkj2 — Tue, 08 Jun 2021 04:34:47 GMT

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.

Re: Moving range calcultion in PowerBI service is slow

daxer-almighty — Tue, 08 Jun 2021 17:43:18 GMT

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.