IF statement performance

[WSeirafi]

Hello,

I'm witnessing a performance issue for a quite simple measure used in a big table.
If I use this measure, the result is instant

Measure =

VAR CurrentVal = SELECTEDVALUE ( Table[Column] )

RETURN

    CurrentVal

However if I use this measure

Measure =

IF (

    ISBLANK (

        SELECTEDVALUE ( Table[Column] )

    ),

    1,

    0

)

The visual goes in error after several minutes loading. What is that due to and is there a way around this ?

Regards

[shafiz_p]

Hi @WSeirafi  With "if" statements, you need to go through each item one by one. When you have a huge table , using "if" statements can be slow. It’s like checking each item individually. The default query timeout is 225 seconds. Please check its performance. Try with switch function, because it faster than "if".

[WSeirafi]

I answered below, unluckily it was not effective

[Greg_Deckler]

@WSeirafi ISBLANK is notorious for bad performance. Try this:

Measure =
IF (
    SELECTEDVALUE ( Table[Column] ) = BLANK(),
    1,
    0
)

or

Measure =
IF (
    SELECTEDVALUE ( Table[Column] ) <> BLANK(),
    0,
    1
)

or

Measure =
SWITCH( SELECTEDVALUE ( Table[Column] ),
    BLANK(), 1,
    0
)

[WSeirafi]

I tried with both suggestions you gave

Measure =

VAR CurrentVal = SELECTEDVALUE ( Table[Column] )

RETURN

    SWITCH(CurrentVal,

        BLANK(),1,

        0

    )
Didn't change anything. There is no other way to do this ?

I would have added a calculated column, but this is supposed to be reacting to a slicer

[Greg_Deckler]

@WSeirafi It's hard to say with the information provided. Would need additional context of the problem you are actually trying to solve. Is this something that could be done using the Filter pane?

[WSeirafi]

Hello,

I ended up merging all my data into one big table for this purpose, but my issue now is that when I use a Switch / if measure in my table, the slicer do not have any effect on it.

I guess it's the fact that the IF measure returns a value for every rown but how can I prevent this from happening ?

Thanks for your help

[WSeirafi]

Might be a solution