Dynamic measure as conditional within a FILTER function destroys performance

DAX
1

I have been wrecking my brain on how to improve my dynamic models, which rely on very large datasets.

The culprit I was able to identify:

Using FILTER() in a CALCULATE() function destroys performance.

This was recently discussed by Chris Webb on his blog.

And even Microsoft themselves will tell you to avoid using FILTER as a filter argument.

(Did they realise how funny that title looks?)

In the same article however, Microsoft tells us:

In this example, the FILTER function must be used. It’s because it requires evaluating the Profit measure to eliminate those months that didn’t achieve a profit. It’s not possible to use a measure in a Boolean expression when it’s used as a filter argument.

And that is exactly my problem:

I run into this limitation all the time. Because most of the models I make are dynamic. By which I mean a slicer filters the data based on for example a Date Selected or similar threshold.

And because the dataset is usually very large, I run into the dreaded Query has exceeded the available resources error far too often.

I see 3 solutions, but I think only the third is going to help me out long term:

  1. Some alternative DAX pattern (which I haven’t heard of yet)
  2. Improving everything apart from the CALCULATE&FILTER context (not efficient or effective)
  3. Restructuring my approach to these dynamic models (in a way I have yet to learn about)

There will probably be a course or 2 on EDNA about (3), but forgive my ignorance as I haven’t found these yet myself.

Demo File

For illustrative purposes, consider the below file.

Note: You will not get the Exceeded Resources error in this file as I am not going to include a +20mb dataset for obvious reasons.

The logic of this Payables Ageing Analysis model relies on the [Days Left] Measure:

Days Left = 
//Days left where Selected Date >= Posting Date AND (Selected Date < Clearing Date OR = BLANK)
IF( AND( [Posting Date] <= [DateSelected], 
    OR([Clearing Date] > [DateSelected], [Clearing Date] = BLANK())),
   DATEDIFF( [DateSelected],[Due Date], DAY ),
   BLANK() )

[DateSelected] takes from the slicer.

This measure is subsequently used as follows for instance:

_A0_ OverdueAmount = 

VAR SelectedColumn = SELECTEDVALUE(SwitchLocalCurrency[CurrencyID])

Var InvoiceValue =
         IF( 
            ISBLANK([Days Left]), 
                BLANK(), 
                SUMX(
                        FILTER(_FACT_BSEG_AP,
                        [Days Left] < 0
                        &&  _FACT_BSEG_AP[H_BLART_DocumentType]<>"KZ"),
                        [_A0_ OutstandingAmount]
                        )
        )

And similar measures for NotDue, for grouping by [Days Left] etc.

Most if not all of which require a CALCULATE-FILTER measure using [Days Left].

If the model has only one [DateSelected] (today, for instance), then this isn’t a problem because I can just add a column in the dataset. But that is not what I am trying to do here.

So what am I missing?

Thank you in advance!

Dynamic_Chart_Interaction_Table.pbix (1.0 MB)

2

Hi @OniScion

Have you tried using DataMentor built within the EDNA platform? Its a great tool to help you solve your problems.

Please give Data Mentor a try!!!
Thanks
Keith

3

Thanks, I did already but the AI modules don’t offer an alternative for my fundamental issue, which is having a FILTER function within the CALCULATE’s filter argument.

The mentioned code in my post got spit out in the identical format, even, save for some indentation and capitalisation.

4

using EDNA learning platform search function there are about 33 items related to “Filter” and about 14 items related to “Calculate”

Did you check out “Showcase” within the EDNA Platform there might be a file there might be able to help you?

I also noticed that you don’t have a separate Date Table within your model. All models should have a Date Table for time intellegent calculations.

thanks
Keith

5

I am willing to accept my approach to dynamic dashboards can use some structural improvements and I have been going through the courses on this matter.

I found my question specific enough for people to also maybe give a specific answer, though:

  1. Calculate ( X, Filter (Measure) ) = Everyone knows is not optimal
  2. However, a dynamic dashboard requires the Calculate to Filter on a Dynamic Measure
  3. Dashboard performance suffers because I don’t have an alternative
  4. What to do?
6

Come to think of it, I did use the “Accounts Receivable Reconciliation Insights” Showcase as inspiration.

The problem (I think) though in both my model and the Showcase is that both

  1. evaluate the entire table, and
  2. use a measure in a filter argument
Outstanding Invoices = 
COUNTROWS( 
    FILTER( Invoices,
        [Days Left] > 0 ) )

That affects performance when dealing with +1 million rows, yet because both models rely on [Days Left] as the logic’s cornerstone, there really isn’t an easy alternative.

What seems to be working, though I am not too sure of it yet, is when I SUMMARIZE the table for the base calculating measure (Outstanding Amounts) like this:

_A2_OutstandingAmount_Company = 
VAR CurrencyRate = [CurrencyModeSelected]
VAR FilteredData = 
    FILTER(
        SUMMARIZE (
                _FACT_BSEG_AP,
                _FACT_BSEG_AP[BUKRS_CompanyCode],
                _FACT_BSEG_AP[DMBTR_AmountLocalCur],
                _FACT_BSEG_AP[DMBE2_USDAmount]
                ),
                NOT ISBLANK([DaysLeft])
    )
VAR InvoiceValue = 
    SWITCH(
        CurrencyRate,
        "Local", SUMX(FilteredData, _FACT_BSEG_AP[DMBTR_AmountLocalCur]),
        SUMX(FilteredData, _FACT_BSEG_AP[DMBE2_USDAmount])
    )
RETURN
    InvoiceValue

And then, instead of [Days Left], I rely on part of its full AND-AND-OR logic with just the date difference:

_A2_NotDueAmount_Company = 
Var FilteredData = 
                FILTER(ALL(_FACT_BSEG_AP[NETDT_NetDueDate],_FACT_BSEG_AP[IsUnallocated]),
                    DATEDIFF([DateSelected],_FACT_BSEG_AP[NETDT_NetDueDate], DAY)>0
                    && _FACT_BSEG_AP[IsUnallocated]=FALSE
                )


    
Var InvoiceValue = 
                CALCULATE(
                    [_A2_OutstandingAmount_Company], 
                    FilteredData
                    )

RETURN
    InvoiceValue

After all, the conditions under which the [Days Left] Measure goes blank (rows that ought to be filtered out) are already in the Outstanding Amount measure, so they don’t have to be repeated within the “NotDue Amount” measure.

I am aware it is best practice to filter columns, not tables. But before I tried SUMMARIZE I was never able to get that to work because of the CALCULATE(FILTER(MEASURE) ) structure.