Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |