Forum Discussion
DAX Switch function with two variables
Dear all
just to collect some thoughts if that is a good way or if this formula will slow down my pbi report in future...
I have two calculated values (Revenue/Units) and a calculated average (ASP). The user can choose from a button which value he wants to see. That works fine and fast with the switch function if i have one variable sum. Now i want to add targets as a second dimension, so i created the following formula:
var_Act vs. Target % = Switch( True();
values(dim_measureswitch[Measure]) = "Revenue"; [var_month_line_chart LC]/[var_Target LC]-1;
values(dim_measureswitch[Measure]) = "Units"; [var_month_line_chart LC]/[var_Target LC]-1;
values(dim_measureswitch[Measure]) = "ASP"; [var_month_line_chart LC]/[var_Target LC]-1;
blank())
var_Target LC = Switch( True();
values(dim_measureswitch[Measure]) = "Revenue"; [Target Sales LC];
values(dim_measureswitch[Measure]) = "Units"; [Target Unit];
values(dim_measureswitch[Measure]) = "ASP"; divide([Target Sales LC];[Target Unit];0);
blank())
var_month_line_chart LC = Switch( True();
values(dim_measureswitch[Measure]) = "Revenue"; ([Sales Revenue LC]-[MTD Sales Revenue LC])+ [MTD Sales Revenue LC RR CD];
values(dim_measureswitch[Measure]) = "Units"; ([Sales Units]-[MTD Sales Units])+ [MTD Sales Units RR];
values(dim_measureswitch[Measure]) = "ASP"; divide(([Sales Revenue LC]-[MTD Sales Revenue LC])+ [MTD Sales Revenue LC RR CD];([Sales Units]-[MTD Sales Units])+ [MTD Sales Units RR];0);
blank())
As you see i have now two variables (one with revenue (var_month_line_chart) and one with targets (var-target LC). That works fine, but the speed is decreasing.
What do you think is this the right way or is there a more efficient way to do this calculation?
KR
Florian
2 Replies
- v-huizhn-msftMicrosoft Employee
Hi Flori_Abb,
From what I know, your formulas are nice, it's normal to run lower after you add more variables. I will post the update when I find more efficient way.
Best Regards,
Angelia- Phil_SeamarkMicrosoft Employee
Hi Flori_Abb
Not so much a performance tip, but I'd recommend using the DIVIDE function for your division maths.
so rather than
z = x / y
use
z = DIVIDE(x,y,0)
In terms of performance, can you preaggregate or precalculate some of your data upstream of Power BI? eg in the Query Editor or in your data source?