Do you have a Power BI report with a table or a matrix on it, where there is a filter on the rows of the table? It’s a very common scenario indeed. Is the table or matrix slow to render? If so, this post is for you!

Consider the following table visual:

There are four columns: Date, Town and two measures. One measure called [Fast Measure] is, as the name suggests, very quick to execute; the other measure, called [Slow Measure], is very complex and slow. The definitions are irrelevant here. Notice that there is a filter on this table visual so only the rows where [Fast Measure] is greater than 1 are shown.

If I measure the amount of time to render this table in Performance Analyzer, it takes around 17.5 seconds to run. However, if I remove the filter on [Fast Measure], the table only takes 8 seconds to run. Why? The filter is on the fast measure and surely more rows are returned without the filter, so wouldn’t the slow measure be evaluated more?

It turns out that the DAX generated for tables with filters applied isn’t as well-optimised as it could be. Yes, I work on the Power BI team and yes this is something that should be addressed, but before that happens I thought it would be good to make as many people aware of this as possible so they can tune their reports. I have worked with a lot of customers recently who are running into this problem without realising it.

Here’s the DAX query for the table without the filter:

DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Date'[Date],
      'Property Transactions'[Town],
      "Fast_Measure", 'Property Transactions'[Fast Measure],
      "Slow_Measure", 'Property Transactions'[Slow Measure]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, 'Date'[Date], 1, 'Property Transactions'[Town], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Date'[Date], 'Property Transactions'[Town]

Here’s the DAX query for the table with the filter:

DEFINE
  VAR __ValueFilterDM0 = 
    FILTER(
      KEEPFILTERS(
        SUMMARIZECOLUMNS(
          'Date'[Date],
          'Property Transactions'[Town],
          "Fast_Measure", 'Property Transactions'[Fast Measure],
          "Slow_Measure", 'Property Transactions'[Slow Measure]
        )
      ),
      [Fast_Measure] > 1
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Date'[Date],
      'Property Transactions'[Town],
      __ValueFilterDM0,
      "Fast_Measure", 'Property Transactions'[Fast Measure],
      "Slow_Measure", 'Property Transactions'[Slow Measure]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, 'Date'[Date], 1, 'Property Transactions'[Town], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Date'[Date], 'Property Transactions'[Town]

Notice how, in this second query, the filter is applied in an additional variable called __ValueFilterDM0. The side effect of implementing the filter in this way is that all the measures in the table, including [Slow Measure], are evaluated twice. This explains why, in this example, the filtered table is twice as slow as the unfiltered table. The performance of your table may be different: it depends on the number of measures, their definition, the nature of the filter and many other factors. Measuring the performance of your table with and without the filter applied will tell you how much of an impact this issue is having in your report.

What can you do about this? One solution is to apply the filter inside the measures rather than on the visual. In this case, creating two new measures with the following definitions:

Fast Measure Filtered =
VAR f = [Fast Measure] 
RETURN IF ( f > 1, f )

Slow Measure Filtered =
IF ( [Fast Measure] > 1, [Slow Measure] )

..and then using these measures on columns instead of the original measures, removing the visual-level filter, like so:

In my example this results in the table rendering in 8 seconds again, but again your mileage may vary. What’s more I’ve turned off totals in the table and therefore avoided the problem of making these measures return the same totals as the totals in the original filtered table. There may be other solutions (I suspect calculation groups would be worth investigating) that work better in your dataset and report.

Are you affected by this problem? How much impact does it have? Do you have a different solution? Let me know in the comments…

A few weeks ago I wrote a post showing how you can use the new EvaluateAndLog DAX function to diagnose performance problems relating to the use of the Switch function. Did you know that calculation groups can experience similar performance problems though? In some scenarios limited evaluation takes place for all calculation items, not just the one you expect; luckily you can use EvaluateAndLog to diagnose this too. In this post I’ll show you a simple example.

Let’s say you have a dataset containing the following table, called Sales:

There’s also a calculation group with three calculation items:

The Measure Value calculation item returns the value of SelectedMeasure but also uses EvaluateAndLog:

EVALUATEANDLOG( SELECTEDMEASURE() )

The Measure Value * 2 and Measure Value * 3 calculation items do the same thing but multiply the value returned by SelectedMeasure by two and three respectively, for example:

EVALUATEANDLOG( SELECTEDMEASURE() * 2 )

There’s another table in the dataset, with no relationship to the Sales table, called Calc Item Names containing a single column listing the three calculation items’ names:

Last of all, there is a measure called Sales Amount:

Sales Amount = SUM(Sales[Sales])

…and a measure called Dynamic Sales Amount Unoptimised that takes the selection made on the Calc Item Names column of the Calc Item Names table and uses it to dynamically select the calculation item with that name and apply it to the Sales Amount measure:

Dynamic Sales Amount Not Optimised =
VAR SelectedCalcItem =
    SELECTEDVALUE ( 'Calc Item Names'[Calc Item Names] )
RETURN
    CALCULATE ( [Sales Amount], 'My Calc Group'[Name] = SelectedCalcItem )

All this allows you to build the following report where a slicer controls the calculation item applied in the Dynamic Sales Amount Not Optimised measure:

As the name suggests, though, there’s a problem with the Dynamic Sales Amount Not Optimised measure that EvaluateAndLog can help uncover. Using Jeffrey Wang’s DAXDebugOutput tool to capture what happens when Measure Value is selected in the slicer, you can see three DAXEvaluationLog events. The first is the one you’d expect: since Measure Value is selected in the slicer, the Measure Value calculation item is evaluated:

The other two events are for the other two, unselected calculation items though – and similar to the Switch problem, some evaluation taking place for all calculation items can cause performance problems:

How do you avoid this problem? It turns out that if you use TreatAs to apply the calculation item inside Calculate, like so:

Dynamic Sales Amount Optimised =
CALCULATE (
    [Sales Amount],
    TREATAS ( VALUES ( 'Calc Item Names'[Calc Item Names] ), 'My Calc Group'[Name] )
)

You only get one DaxEvaluationLog event when the query runs and only the calculation item you selected is evaluated:

[Thanks to Krystian Sakowski for originally diagnosing the problem in this post and coming up with the fix]