Forum Discussion

DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
9 months ago
Solved

Is 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, ...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    8 months ago

    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 Learn

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

Mauro89's avatar
Mauro89
Icon for Super User rankSuper User
9 months ago

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 Reason
IN {…} inside CALCULATEBestUses optimized filter propagation & storage engine
FILTER(...) + MINXWorstForces row-by-row evaluation in the formula engine

 

Best regards!

 

If you find this helpful, consider some kudos or mark it as solution

DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
9 months ago

thus you propose:

measure = calculate ( min ( field ) , filter ( 'table' , field2 IN { "A" , "B" } ) )

? 

  • Mauro89's avatar
    Mauro89
    Icon for Super User rankSuper User
    9 months ago

    Yes, "calculate" with "in".

    • DouweMeer's avatar
      DouweMeer
      Icon for Impactful Individual rankImpactful Individual
      9 months ago

      Seems to run worse, goes from 18 GB to 64 GB and blowing its roof compared to minx. 
      Let me get it better. It runs through 6,5 million record on a table of 4500 records. 

      Bit like this:

      measure = 
VAR a1 = 
calculate ( min ( ) , filter ( table , boolean ) )
VAR a2 = 
calculate ( min ( ) , filter ( table , different boolean ) )
RETURN
Switch ( true ( )
 , a1 <> blank ( ) , "something" 
 , a2 <> blank ( ) , "something else" 
 )