Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

10x slower with ALLEXCEPT vs

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]
)
1 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
v-gizhi-msft
Community Support
Community Support

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
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.