- Microsoft Power BI Community
- Welcome to the Community!
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish Community
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Slow Measures

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Slow Measures

06-15-2018
07:42 AM

I have report that analyzes data on customers. Data is filtered by three slicers, one for customer, another for data date, and the third for internal business unit (responsible for the customer).

Certain calculations are then made (via measures), and finally a letter grade is assigned based on the how those calculations score.

All of the measures in this report function quickly, within a matter of seconds, except the letter grade ones, which usually take minutes. Why is this? They are easily the simplest measures in the report.

Here is a sample of their code:

Grd_ApptDesir = IF([Scr%_ApptDesir]>=.9 , "A" , IF(AND([Scr%_ApptDesir]<.9,[Scr%_ApptDesir]>=.8) , "B" , IF(AND([Scr%_ApptDesir]<.8,[Scr%_ApptDesir]>=.7) , "C" , IF(AND([Scr%_ApptDesir]<.7,[Scr%_ApptDesir]>=.6) , "D" , "F" ) ) ) ) )

Any ideas why this is occuring? How to make it better?

Thanks!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2018
01:28 PM

With this measure, you are calling, and calculating the [Scr%_ApptDesir] measure 7 times...it only needs to be calculated once.

try this:

Grd_ApptDesir = VAR Score = [Scr%_ApptDesir] RETURN SWITCH ( TRUE (), Score >= .9, "A", Score >= .8, "B", Score >= .7, "C", Score >= .6, "D", "F" )

13 REPLIES 13

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2018
01:28 PM

With this measure, you are calling, and calculating the [Scr%_ApptDesir] measure 7 times...it only needs to be calculated once.

try this:

Grd_ApptDesir = VAR Score = [Scr%_ApptDesir] RETURN SWITCH ( TRUE (), Score >= .9, "A", Score >= .8, "B", Score >= .7, "C", Score >= .6, "D", "F" )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-24-2023
06:42 AM

I know this is an older post, but I am having the same issue with my report. Is my below measure more complex than it needs to be? I have several measures within my report, but it seemed to slow down quite a bit after adding this one.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2018
02:55 PM

@Anonymous Thank you! I had considered using switch statements, but I wasn't sure how to use them. Thank you for a good example!

I modified those measures with switch statements, and load times for those grade measures took over 60% less time! I'd call that a solution.

I had suspected that Power BI recalculates measures when you call them, but not multiple times in the same measure! That seems buggy...

Also, those Scr% measures are based off of several others, so I just attributed the slow lead times to those "nested" calls. I have a thread that got lost somewhere about "3rd Tier Measures".

Thanks for your help!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2018
05:17 PM

@jengwt, no worries!

I realize that it seems buggy, but consider this:

Everytime the measure is called in an expression, the filter and/or context may be different. That is why it needs to be evaluated separately each time.

Paste the other measures that are referenced in the final measure, we may be able to optimize those as well.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-18-2018
07:24 AM

@Anonymous That's true, but PBI already calculates a given measure any time you change a slicer. So why isn't a given measure just stored as a variable on the back end? It only needs to change if the user changes it's filtering, and any number of other measures, columns, etc, should be able to call it at will.

I'm already at work looking to add in switch statements to speed things up in other places in this report and other reports that I've made. What other types of recursive statements are there? A "switch" is basically a "case". What about "for/foreach"s?

Here is a sample of one of the Scr%s. Some of what I do here is necesary because of the way the data is structured.

[m_ApptTimes] is simply a sum(x)/sum(y)

I used [m_ApptTimes] > AVERAGE... because I have to use a function in a CALCULATE, and the average of one item is simply itself. However, if for some weird reason there are multiple values, using average should lessen the damage, vs a sum, product, etc.

In short, the Scr% codes check to see if the a metric falls within a certain range. If it it outside the range, it will produce a 100% or 0%. Otherwise, the percentage is calculated based on the range.

Now, these still load quite quickly. The worse offender by far is still the letter grade measures above.

Scr%_ApptDesir = IF(CALCULATE([m_ApptTimes] < AVERAGE('CUS_RANGE'[MIN_VALUE]) , FILTER('CUS_RANGE' , AND('CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "") , AND('CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR" , 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR" ) ) ) ) , 1 , IF(CALCULATE([m_ApptTimes] > AVERAGE('CUS_RANGE'[MAX_VALUE]) , FILTER('CUS_RANGE' , AND('CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "") , AND('CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR" , 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR" ) ) ) ) , 0 , CALCULATE( (1 - ( ([m_ApptTimes] - AVERAGE('CUS_RANGE'[MIN_VALUE])) / (AVERAGE('CUS_RANGE'[MAX_VALUE]) - AVERAGE('CUS_RANGE'[MIN_VALUE])) )) , FILTER('CUS_RANGE' , AND('CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "") , AND('CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR" , 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR" ) ) ) ) ) )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-18-2018
08:49 AM

@jengwt, this will optimize your measure a little bit:

Scr%_ApptDesir = VAR FilteredTable = FILTER ( 'CUS_RANGE', 'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE ( 'MTHLY'[IBU], "" ) && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR" && 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR" ) RETURN IF ( CALCULATE ( [m_ApptTimes] < AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), FilteredTable ), 1, IF ( CALCULATE ( [m_ApptTimes] > AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), FilteredTable ), 0, CALCULATE ( ( 1 - DIVIDE ( ( [m_ApptTimes] - AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) ), ( AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ) - AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) ) ) ), FilteredTable ) ) )

You're using the same filter arugment in 3 different instances, so there's no need to scan the table 3 times to make the same table. Just assign it to a variable and off you go.

I'm also a little curious about this expression. is it a calculated column, or a measure? If it's a measure, I would do this to further optimize:

Scr%_ApptDesir = VAR FilteredTable = FILTER ( 'CUS_RANGE', 'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE ( 'MTHLY'[IBU], "" ) && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR" && 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR" ) VAR AverageMin = AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) /*Or this CALCULATE( AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), FilteredTable ) */ VAR AverageMax = AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ) /*Or this CALCULATE( AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), FilteredTable ) */ RETURN IF ( CALCULATE ( [m_ApptTimes] < AverageMin, FilteredTable ), 1, IF ( CALCULATE ( [m_ApptTimes] > AverageMax, FilteredTable ), 0, CALCULATE ( ( 1 - DIVIDE ( ( [m_ApptTimes] - AverageMin ), ( AverageMax - AverageMin ) ) ), FilteredTable ) ) )

I can't tell if you want the TRUE/FALSE statement to be in the calculate expression, or if you want to compare the [m_ApptTimes] measure to the Average of the Min/Max column, and have both of them respect the filtered table variable.

Even still, the first expression will be at least a little faster.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-18-2018
11:29 AM

@Anonymous Implemented those changes. Page refreshes down to 10 seconds! This is what I've got them looking like now:

Scr%_ApptWinHrs = VAR FilterTable = FILTER('CUS_RANGE' , 'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "") && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR" && 'CUS_RANGE'[METRIC_NAME] = "APPT_WIN_HR") VAR MinVal = CALCULATE(AVERAGE('CUS_RANGE'[MIN_VALUE]), FilterTable) VAR MaxVal = CALCULATE(AVERAGE('CUS_RANGE'[MAX_VALUE]), FilterTable) VAR Metric = [m_ApptWinHrs] RETURN SWITCH( TRUE() , Metric > MinVal, 1 , Metric < MaxVal, 0 , (Metric - MaxVal) / (MinVal - MaxVal) )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-18-2018
08:18 PM

That last measure looks great! I figured that's where you were heading towards, but I couldn't quite parse it out the way you had it first written.

Always use variables. If you evaluate the same expression more than once, make it a variable.

2 other pieces of advice:

- use DIVIDE() on your RETURN statement.
- Just in case MinVal = MaxVal, you'll catch it with DIVIDE and you won't return a divide by 0 error.
- If you're 100% sure you'll never come across that error (very well may be the case, but I don't know your data), you can ignore it. It will slow down performance just a touch, but it's much faster than using an IF() to catch dividing by 0.

- Don't use FILTER() to filter the entire 'CUS_RANGE' table.

FILTER() is an iterator, so it will look at EVERY row in the table in the current filter context, and include the row if it passes the 3 criteria that you set. I would recommend the following code for VAR FilteredTable:

VAR CurrentBusUnit = SELECTEDVALUE ( 'MTHLY'[IBU], "" ) VAR FilterTable = CALCULATETABLE ( 'CUS_RANGE', 'CUS_RANGE'[BUS_UNIT] = CurrentBusUnit, 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR", 'CUS_RANGE'[METRIC_NAME] = "APPT_WIN_HR" ) ...rest of the measure

Instead of looking at every row in the current filter context, this expression will evaluate the entire table in the context of the 3 filters. Should take you below 10 seconds.

You may be able to replace that first parameter of CALCULATETABLE() (the 'CUS_RANGE') with a

VALUES( 'CUS_RANGE'[ColumnName] )

if you have a unique identifier column. That will then return only the distinct list of values for that specific column where the other 3 columns are as specified. That single column table will then act as the filter for everything else.

In short, don't use FILTER() on an entire table if you can get around it.

Good luck!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-04-2018
03:05 PM

Based on this discussion, I think my DAX code would benefit from optimization. My report takes up to a minute to load visuals after making a slicer selection, and it makes the report unusable. I'd appreciate any suggestions to improve my work!

I have a central fact table, Jobs, and I'm trying to add columns with data from other related fact tables (using the ID field). Item1, Item2, etc. data are spread across a handful of tables and I'm trying to get counts for Fail and Pass for all Items all in one place:

Results = ADDCOLUMNS(Jobs, "Item1_PASS", COUNTAX ( FILTER ('DataTable1', 'DataTable1'[Item1] = "Pass" && 'DataTable1'[ID]=Jobs[ID]), 'DataTable1'[Item1]), "Item1_FAIL", COUNTAX ( FILTER ('DataTable1', 'DataTable1'[Item1] = "Fail" && 'DataTable1'[ID]=Jobs[ID]), 'DataTable1'[Item1]),

My understanding was that I need to create measures (in a separate measure table) that calculate a sum for each of the columns that I'm adding to Jobs:

Item1_FAIL **measure:**

Item1_FAIL = SUM('Results'[Item1_FAIL])

The end goal is a visual with counts of Pass and Fail for each Item that will work with slicers that use other columns from Jobs, like this:

Fail Count = SWITCH ( FIRSTNONBLANK( 'Item_key'[Item Name], 1), "Full description for Item1.", 'My Measures'[Item1_FAIL],

Again, this report has become very frustrating to use because of the load times - I have over 200 Items to display results for. I imagine I could set up my code in a better way, but I'm not sure what that would be. Thanks in advance for suggestions, and please let me know where I can clarify anything.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-05-2018
07:29 AM

@jscottNRG, please post this response in a new thread, and mention me in it.

I think it's best practice to keep a thread to a single issue. Measure Optimization is a broad topic, but this deserves its own thread.

Also, please include a picture of your relationships so that I can see the other dimension tables as well.

Thanks,

~ChrisHaas

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-18-2018
09:56 AM

@Anonymous the Scr%s are measures. A few tweaks, and I think this will work well.

To answer your question, we're merely checking to see if the calculated appointment times [m_ApptTimes] are greater than the min or the max allotted (contained with many others in table 'CUS_RANGE'). So, the two ways you've coded it would need to be altered a little to be like this:

RETURN IF ( [m_ApptTimes] < CALCULATE ( AVERAGE('CUS_RANGE'[MIN_VALUE]), FilteredTable ), 1, ...

or,

VAR AverageMin = CALCULATE( AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), FilteredTable ) VAR AverageMax = CALCULATE( AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), FilteredTable ) RETURN IF ( [m_ApptTimes] < AverageMin, 1, IF ( [m_ApptTimes] > AverageMax, 0, ...

So, it would depend on which of the two methods used. Would it be faster to forgoe creating those variables, as each is only called twice anyways?

Thanks again! You've really advanced my knowledge of DAX. I didn't know that DAX could do half of the things that you've done in this code. I will go back and review more of my code, and see where I can use your suggestions.

I think the the VARs will be particularly useful.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2018
08:43 AM

I'm not sure why you're using an AND function in every bit of your measure, the way it's coded the first requirement is completely superfluous (e.g. in the second stage, it must be less than 0.9, as if it was 0.9 or more it's not reaching the second IF in the first place), don't know whether that'd make a huge difference in performance though

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-15-2018
08:48 AM

@jthomson That's true, I just prefer to have everything defined for the freak occurences / Power BI glitches.

Nonetheless, as per your suggestion, I took out the redundant ANDs, and there is no perceptable change.

Announcements

Featured Topics

Top Solution Authors

User | Count |
---|---|

142 | |

61 | |

61 | |

58 | |

49 |

Top Kudoed Authors

User | Count |
---|---|

139 | |

71 | |

63 | |

62 | |

55 |