Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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]
)
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,
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
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
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |