Slowness with measure used for conditional formatting

[MiraNi]

I have a visual with lots of measures. Without alarm colors it is rendering very fast in Power BI service. But when I add alarm colors, rendering gets slow. 

I have actual MTD, actual YTD and target -measures in visual for three different types of KPI, 12 measures as total. Alarm color is defined for all actual values by using alarm color measure value.

Alarm color measure dax for actual YTD measure looks like this:
VAR CY = '_KPI Measures'[KPI % YTD]

VAR TARGET = '_KPI Measures'[KPI target %]

VAR LYTOTAL = '_KPI Measures'[KPI % LY total]

RETURN

IF(

CY = BLANK(),

BLANK(),

IF(

OR(

CY = TARGET ,

CY > TARGET

),

[AlarmColorGreen],

IF(

OR(

CY = LYTOTAL,

CY > LYTOTAL

),

[AlarmColorYellow],

IF(

CY < LYTOTAL,

[AlarmColorRed],

[AlarmColorBlack]

)

)

)

)

All values used in alarm color measure, except last year total, are already included in visual. 

How could I improve the alarm color measure, when none of the measures used in it's DAX are not slow when existing in visual, only when used in alarm color?

[v-henryk-mstf]

Hi @MiraNi ,

Looking directly from the formula, no problems were found. But for the KPI% YTD, KPI target% and KPI% LY total fields quoted in the above formulas, are they from the original data source or the created measure formula? If it comes from the created measure formula, it may affect the calculation logic and affect the calculation speed. Can you provide test data (delete sensitive information) and expected results, so that I can answer you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[MiraNi]

Hi,

yes, the reason might be behind the data model, there are lots of different time periods calculated for one visual. The measures visible in a visual are actual LD, MTD, YTD and target, all three times for each different KPI's. And then these alarm color measures are used for conditional formatting - each actual measure needs to have own alarm color measure for the same time period. I have a sample data with transformed values including a data model diagram. But there is no place here to add an Excel file, only photos etc.

I ran the DAX query of the slow visual in Dax studio and most of the time is spend at the formula engine.

[v-henryk-mstf]

Hi @MiraNi ,

As you said, most of the time is spend at the formula engine. I provide a link to a blog that has a good explanation of the roles of the formula engine and of the storage engine used to execute DAX queries, which can also deepen your understanding. Hope to help you. 

Formula engine and storage engine in DAX - SQLBI

Looking forward to your feedback.

Best Regards,
Henry

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

[MiraNi]

Hi, 

Thank you! I cannot find the reason for slowness, so I would still like to attach a sample data and data model diagram if you can guide me, how I can do that? I cannot find any option to attach a file here.

Rgds, Mira

[amitchandak]

@MiraNi , I am not sure if that measure logic is correct or not,

Tried the modified version

VAR CY = '_KPI Measures'[KPI % YTD]
VAR TARGET = '_KPI Measures'[KPI target %]
VAR LYTOTAL = '_KPI Measures'[KPI % LY total]
RETURN
Switch( True()
isblank(CY) ,BLANK(),
CY >= TARGET , [AlarmColorGreen],
CY >= LYTOTAL,[AlarmColorYellow],
CY < LYTOTAL,[AlarmColorRed],
[AlarmColorBlack]
)

[MiraNi]

@amitchandak Thank you for this. I tested it but it didn't help.