Forum Discussion
Impactful IndividualIs IN short circuit?
As title, is IN short circuit?
Like
measure = calculate ( min ( field ) , field2 IN { "A", "B" } )if field2 has the value of "A", would it also verify it has "B"?
Wouldn't really make sense, just curious whether I would write it as above or as:
measure = calculate ( min ( field ) , field2 = "A" || field2 = "B" )or different:
measure = minx ( filter ( 'table' , field2 = "A" || field2 = "B" ) , field )Which in theory is the fastest?
Hi DouweMeer,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to danextian, Mauro89, amitchandak, for those input on this thread. Thanks for sharing the details about the 6.5M rows that changes the situation quite a bit.
The performance issue isn’t caused by IN vs || syntax. The slowdown happens because the logic is currently inside a calculated column, which forces Power BI to run your MINX/FILTER calls once for every row during refresh. That’s why memory usage spikes from 18 GB → 64 GB.
To resolve this, I recommend moving the logic into a measure instead. Measures only run when visuals request data and allow the storage engine to optimize filter reduction.Example pattern:
Measure = VAR A1 = CALCULATE( MIN('table'[field]), KEEPFILTERS(boolean) ) VAR A2 = CALCULATE( MIN('table'[field]), KEEPFILTERS(different_boolean) ) RETURN SWITCH( TRUE(), NOT ISBLANK(A1), "something", NOT ISBLANK(A2), "something else" )This should remove the RAM pressure and significantly improve performance.
Refer these links:
1. CALCULATE function (DAX) - DAX | Microsoft Learn
2. FILTER function (DAX) - DAX | Microsoft Learn
3. Avoid using FILTER as a filter argument in DAX - DAX | Microsoft LearnHope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
15 Replies
- amitchandak
Super User
DouweMeer , OR and In should be the same, with IN as a better choice. but Calculate with Without filter has a difference , in this case it is applicable for Minx
http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/
https://youtu.be/KDcmzwgPvXQ
DouweMeerThe link you proposes doesn't seem to work.
But you would propose
measure = calculate ( min ( field ) , filter ( 'table' , field2 IN { "A" , "B" } ) )As most effective?
- amitchandak
DouweMeer , Depends, with filter is a better choice when it is most needed. When you want to show the same value across field2, which is the sum of two values (Only for the filter column) for all other columns and GT it will give same value , you need without filter
Calculating without FILTER is generally better for performance because it's less complex
https://learn.microsoft.com/en-us/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument
- Mauro89
Hi DouweMeer,
as of my knowledge no — IN is not a short-circuit operator in DAX. You should not assume that it stops evaluation after the first match. The optimizer chooses the best execution plan internally, and the logic is set-based, not sequential.
All three produce the same result, but performance differs:
Approach Performance ReasonIN {…} inside CALCULATE Best Uses optimized filter propagation & storage engine FILTER(...) + MINX Worst Forces row-by-row evaluation in the formula engine Best regards!
If you find this helpful, consider some kudos or mark it as solution
v-kpoloju-msftCommunity Support
Hi DouweMeer,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to danextian, Mauro89, amitchandak, for those input on this thread. Thanks for sharing the details about the 6.5M rows that changes the situation quite a bit.
The performance issue isn’t caused by IN vs || syntax. The slowdown happens because the logic is currently inside a calculated column, which forces Power BI to run your MINX/FILTER calls once for every row during refresh. That’s why memory usage spikes from 18 GB → 64 GB.
To resolve this, I recommend moving the logic into a measure instead. Measures only run when visuals request data and allow the storage engine to optimize filter reduction.Example pattern:
Measure = VAR A1 = CALCULATE( MIN('table'[field]), KEEPFILTERS(boolean) ) VAR A2 = CALCULATE( MIN('table'[field]), KEEPFILTERS(different_boolean) ) RETURN SWITCH( TRUE(), NOT ISBLANK(A1), "something", NOT ISBLANK(A2), "something else" )This should remove the RAM pressure and significantly improve performance.
Refer these links:
1. CALCULATE function (DAX) - DAX | Microsoft Learn
2. FILTER function (DAX) - DAX | Microsoft Learn
3. Avoid using FILTER as a filter argument in DAX - DAX | Microsoft LearnHope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
- v-kpoloju-msft
Hi DouweMeer,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
- v-kpoloju-msft
Hi DouweMeer,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.
