Forum Discussion

Flori_Abb's avatar
Flori_Abb
Helper I
9 years ago

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-msft's avatar
    v-huizhn-msft
    Microsoft 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_Seamark's avatar
      Phil_Seamark
      Microsoft 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?