Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MiraNi
Advocate III
Advocate III

Slowness with measure used for conditional formatting

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?
6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

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

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.

MiraNi_0-1617720197554.png

 

 

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. 

 

v-henryk-mstf_0-1617788875990.png

 

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.

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
Super User
Super User

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors