cancel
Showing results for
Did you mean:
Highlighted
Resolver I

## Slow Measures

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!

12 REPLIES 12
Highlighted
Anonymous
Not applicable

@jengwt

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.

• 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!

Highlighted
Helper II

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:

"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:

visual settingsvisual example

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.

Highlighted
Anonymous
Not applicable

@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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors