10x slower with ALLEXCEPT vs

[Anonymous]

I'm calculating a variable and initially used ALLEXCEPT as a filter for MAXX, only to find it was incredibly slow (250 seconds to calculate a single Measure). Switching the logic to CALCULATE with a column filter was 10x faster (25 seconds, still slow...)

Can anyone help me understand why ALLEXCEPT performs so badly, and what else must be happening in my data to make it so bad (obviously it works fine in other circumstances)

CALCULATE:

VAR _prevdate =
CALCULATE(
 MAX(Appointments[Appointment Start]), 
 Appointments[Appointment Start] < _date
)

ALLEXCEPT:

var _prevdate = MAXX(
 FILTER(
 ALLEXCEPT(Appointments,Appointments[Member Type]),
 Appointments[Appointment Start] < _date && NOT(ISBLANK([Number of appointments]))
 ),
 Appointments[Appointment Start]
)

[v-gizhi-msft]

Hi,

It is becasue of MAXX and MAX.

If you use MAXX and ALLEXCEPT to calculate, the formula will calculate the table after applied filter conditions first, and then calculate the max value based on this filtered table.

If you use CALCULATE and MAX to calculate, the formula will directly calculate the max value.

So i advise you using CALCULATE+MAX instead of MAXX+ALLEXCEPT(ALL/ALLSELECTED).

Best Regards,

Giotto

[Greg_Deckler]

Those are not equivalent calculations. The second one will maintain filters on the "Member Types" column while the first one will not. So, you are comparing apples and oranges.

[Anonymous]

Hi @Greg_Deckler - appreciate you writing back to help me out!

Somehow, they give the same results when I change the filter selection on the Member Types column...just works 

I do seem to be having problems with using FILTER, though - extremely slow queries, visuals running out of memory. Do you know if FILTER is just an inefficient function to use when you have big tables or you're using DirectQuery mode? Anything to help me figure this out would help, I'm quite stuck.

[Greg_Deckler]

@Anonymous As @edhans explained, same results do not make them equivalent at all.

I also agree with @edhans about CALCULATE. I don't like using it if at all possible. I personally do not consider it very good code. I have a 500+ page book on DAX with 120+ different DAX recipes and I don't believe CALCULATE appears in it a single time.

Performance tuning is an evolving discipline in DAX. 

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

[edhans · Accepted solution]

@Anonymous They might be returning the same results because of how your visual is, but they are not the same calc at all. This is the filter context (model filter, visuals, slicer, etc) that is returning the same result.

I don't now how big your table is, but ALLEXCEPT() can be very inefficent. Let's say you have 100 columns and 1,000,000 rows. You basically told the model to remove filters on 99 columns. It may be more efficent to use ALL() to remove the filters on just what you need, like below:

var _prevdate =
MAXX(
    FILTER(
        ALL(
            Appointments[Field XType]
            Appointments[Field YType],
            Appointments[Appointment Start]
        ),
        Appointments[Appointment Start] < _date
            && NOT (
                ISBLANK( [Number of appointments] )
            )
    ),
    Appointments[Appointment Start]
)

FILTER() is definitely not your problem. In fact, in your first measure, it is using Filter. It is just syntax sugar that is alowing you to not use the function directly. In the background, it is doing this:

Measure =
CALCULATE(
    MAX( Appointments[Appointment Start] ),
    FILTER(
        ALL('Appointments'),
        Appointments[Appointment Start] < _date
    )
)

Which means you can probably just use this:

Measure =
MAXX(
    FILTER(
        ALL( 'Appointments' ),
        Appointments[Appointment Start] < _date
    ),
    Appointments[Appointment Start]
)

CALCULATE() does something called context transition and can be very expensive, and can be necessary. But your measure may not require context transition, so it isn't necessary to do it. I avoid CALCULATE() for this reason unless I know I need it. It doesn't matter on small models with 10,000 records, but it can matter on tables with millions of records depending on what it has to do. There are two chapters on just the intricacies of CALCULATE() in the Definitive Guide to DAX, and even more info in subsequent chapters.

FILTER() is the most efficent of the DAX measures because everything is a filter for DAX. It is all about tables and filters.

[Anonymous]

Thanks @edhans and @Greg_Deckler  - I'm trying to apply your advice now to my model

[edhans]

Great @Anonymous - glad to be of assistance. If you have any questions during development, post back with a new thread for help on specifics.