Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have referred the below link to create moving range to create Individual-Moving range chart in Power BI:
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.
Solved! Go to 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]
)
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]
)
@daxer-almightyThanks. If RecordID is not unique, what should I do in that case?
Then you have a data quality problem and should clean the data.
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.
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.
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.
"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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |