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 have created a multiple IF statement but the statement is taking too long to execute, is there a way around this?
My measure:
Solved! Go to Solution.
Have you tried using 'SWITCH'?
Trend = SWITCH( TRUE() ,
[count negative results] = 6 , "Losing" ,
[count negative results] in {3,4,5} && [Average Moving and CY TTM] <= -.6 , "Losing" ,
etc....
This has sped up my nested IF statements in the past.
Have you tried making a call the measures and storing as variables outside of the IF/SWITCH ?
Trend = var __CountNeg = [Count Negative Results] var __AvgMov = [Average Moving and CY TTM] RETURN SWITCH(TRUE(), __CountNeg = 6, "Losing", __CountNeg in {3,4,5} && __AvgMov <= -.6 ,"Losing", ...
Have you tried using 'SWITCH'?
Trend = SWITCH( TRUE() ,
[count negative results] = 6 , "Losing" ,
[count negative results] in {3,4,5} && [Average Moving and CY TTM] <= -.6 , "Losing" ,
etc....
This has sped up my nested IF statements in the past.
Thanks for the reply,
It worked faster than IF Statement, however, it still takes around 5 minutes to execute, is there any other workaround?
Thanks again!
Next thing i would think of is looking at the measures you are referencing in statement and see if there is a way you could run those more efficiently.
Also maybe VAR them in the function instead of referencing them? I definately think 5 minutes is too long to run that statement though.
Hey,
Thanks for the above inputs, I have different measures and i am counting the frequency of negative values of those measure. I dont find any other way to add these measures, please help!
Have you tried making a call the measures and storing as variables outside of the IF/SWITCH ?
Trend = var __CountNeg = [Count Negative Results] var __AvgMov = [Average Moving and CY TTM] RETURN SWITCH(TRUE(), __CountNeg = 6, "Losing", __CountNeg in {3,4,5} && __AvgMov <= -.6 ,"Losing", ...
Worked like a charm! Thank you all!
Hey @Anonymous,
please mark the most helpful post as an answer, as it also helps others in this forum.
Regards,
Tom
Instead of a measure try doing that for a calculated column. This way it will do the calculation when it brings in the data rather than at the time you are trying to run the query.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |