Improve XmR Graph visual performance - Need Help

[KrisKolady]

I'm using a "Line and Stacked Column Chart" visual to show an XMR chart with avg and limit lines.

Here are all the measures that go into the visual

The issue is that the visual is too slow - takes almost 6 seconds to update. If I remove many of the limit lines the performance gets back to under a second. I think the issue is that I'm recomputing [XMR Avg Val] and [XMR Avg mR] measure for each of the control lines. Here is an example of these two measures, and one of the control lines:

XMR Avg Val =

VAR LastDatePreviousMonth = EOMONTH(TODAY(), -1)

VAR LastPeriodDate = DATE( YEAR(LastDatePreviousMonth) - 1, MONTH(LastDatePreviousMonth), DAY(LastDatePreviousMonth) )

VAR Period = DATESINPERIOD( DateCalendar[Date], LastPeriodDate, -24, MONTH )

RETURN

    CALCULATE(

        AVERAGEX( VALUES(DateCalendar[YearMon]), [XmR Value] ),

        Period

    )

XmR Avg mR =

VAR LastDatePreviousMonth = EOMONTH(TODAY(), -1)

VAR LastPeriodDate = DATE( YEAR(LastDatePreviousMonth) - 1, MONTH(LastDatePreviousMonth), DAY(LastDatePreviousMonth) )

VAR Period = DATESINPERIOD( DateCalendar[Date], LastPeriodDate, -24, MONTH )

RETURN

    CALCULATE(

        AVERAGEX( VALUES(DateCalendar[YearMon]), [XmR mR] ),

        Period

    )

// Example of the lower control limit measure

XmR Chart LCL = MAX([XMR Avg Val] - 2.66 * [XmR Avg mR], 0)

I'm wondering whether there is a more elegant way to do this where the [XmR Avg Val] and [XmR Avg mR] is pre-computed and can be used across the different control line measures but also get re-computed as different filters are applied.

Any other suggestions to make the visual or measures more efficient will also be greatly appreciated. Thank you,

Kris 

[Anonymous · Accepted solution]

Hi @KrisKolady, 

Consider combining the calculations for XMR Avg Val and XmR Avg mR into a single measure, you can avoid redundant calls to TODAY() and the time range logic.

Sample code:

XMR Avg Combined =
VAR LastDatePreviousMonth = EOMONTH(TODAY(), -1)
VAR LastPeriodDate = DATE(YEAR(LastDatePreviousMonth) - 1, MONTH(LastDatePreviousMonth), DAY(LastDatePreviousMonth))
VAR Period = DATESINPERIOD(DateCalendar[Date], LastPeriodDate, -24, MONTH)
VAR AvgVal = CALCULATE(
    AVERAGEX(VALUES(DateCalendar[YearMon]), [XmR Value]),
    Period
)
VAR AvgMR = CALCULATE(
    AVERAGEX(VALUES(DateCalendar[YearMon]), [XmR mR]),
    Period
)
RETURN
    AvgVal & "|" & AvgMR

Next, use this combined measure in your control line calculations:

XmR Chart LCL =
VAR Combined = [XMR Avg Combined]
VAR AvgVal = VALUE(LEFT(Combined, FIND("|", Combined) - 1))
VAR AvgMR = VALUE(MID(Combined, FIND("|", Combined) + 1, LEN(Combined)))
RETURN MAX(AvgVal - 2.66 * AvgMR, 0)

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KrisKolady]

Joyce,

Thanks for the proposed solution. That's a great way to optimize the measure and I'll use it.

In parallel, I was trying to solve the performance issue and the thing that really helped it was when I used PowerQuery to compress the fact table from date level to month level granularity and removed a lot of the extraneous data that I was not using for the visual. It improved the performance 10X - from 6000 to 500 millisecs.

Thanks for taking the time to propose a solution.

Kris K