Variables performance on filter

[jlrozano]

Hi,

I already read post about variables perfomances and this article: Optimizing DAX expressions involving multiple measures - SQLBI. In them, it is recommended to always use variables and the least number of queries possible. However, my experience is the opposite.

I have a matrix by categories and years. For certain categories, I have to perform an additional filter:

VAR CategoryId = SELECTEDVALUE(Categories[Id])

VAR FilterText = SWITCH(CategoryId,
        110, "A",
        111, "B",
        62, "C",
        126, "D",
        127, "E", BLANK())

RETURN IF(ISBLANK(FilterText), SUMX(Facts,[Value]), SUMX(FILTER(Facts, [NegocioBP] = FIlterText), [Value])

For this code, i get times over 35000 - 40000 ms. But if use this other code I get times over 950 -1100 ms.

VAR CategoryId = SELECTEDVALUE(Categories[Id])

VAR A = SUMX(FILTER(Facts,   [NegocioBP] = "A" ),[Value])
VAR B = SUMX(FILTER(Facts,   [NegocioBP] = "B" ),[Value])
VAR C = SUMX(FILTER(Facts,   [NegocioBP] = "C" ),[Value])
VAR D = SUMX(FILTER(Facts,   [NegocioBP] = "D" ),[Value])
VAR E = SUMX(FILTER(Facts,  [NegocioBP] = "D" ),[Value])
VAR F = SUMX(Facts,  [Value])

RETURN 
SWITCH(CategoryId,
        110, A,
        111, B,
        62, C,
        126, D,
        127, E, F)

Why?..

Thanks.

[amitchandak]

@jlrozano , In the first case in the second sumx in return. code has to execute for all rows

SUMX(Facts, [NegocioBP] = FIlterText)

while the second code is at the visual level on the category of row

That is what I can get

[jlrozano]

Hi,

Thanks for the reply. I don't understand why first case is for all rows an the second is a visual level. I think both is at the visual level on the category of row. Both received the same dataset and filter it, but the fisrt one with sumx and a variable condition and the second perform six filter, one for each case, with a fixed condition.