 # Optimising DAX formula

Hi All

Gone through my code, and most of it is pretty simple coding, with summary DAX appears to have many duplicated words. Could someone help me re-write them in a more optimal manner:

DAX 1)
CY KPI =
VAR CURRENTITEM = TRIM(SELECTEDVALUE( ‘Exec Summary’[KPI]))

RETURN
SWITCH( TRUE() ,
[Period Table Data] = “MTD” && CURRENTITEM = “Total Revenues”, [TORE MTD] ,
[Period Table Data] = “MTD” && CURRENTITEM = “EBITDA”, [EBITDA MTD] ,
[Period Table Data] = “MTD” && CURRENTITEM = “EBITDA %”, FORMAT([EBITDA % MTD] , “0.0%”) ,
[Period Table Data] = “YTD” && CURRENTITEM = “Total Revenues”, [TORE YTD] ,
[Period Table Data] = “YTD” && CURRENTITEM = “EBITDA”, [EBITDA YTD] ,
[Period Table Data] = “YTD” && CURRENTITEM = “EBITDA %”, FORMAT([EBITDA % YTD] , “0.0%”) )

DAX 2)
v Bud =
SWITCH( TRUE() ,
[Exec Var Table Data] = “Var.” && [Exec Table Data]= “EBITDA %” && [Period Table Data] = “MTD” , FORMAT( [EBITDA % MTD] - [Bud EBITDA % MTD] , “0.0%” ) ,
[Exec Var Table Data] = “Var.” && [Exec Table Data]= “EBITDA %” && [Period Table Data] = “YTD” , FORMAT( [EBITDA % YTD] - [Bud EBITDA % YTD] , “0.0%” ) ,
[Exec Var Table Data] = “% Var.” && [Exec Table Data]= “EBITDA %” && [Period Table Data] = “MTD” , FORMAT( [EBITDA % MTD] - [Bud EBITDA % MTD] , “0.0%” ) ,
[Exec Var Table Data] = “% Var.” && [Exec Table Data]= “EBITDA %” && [Period Table Data] = “YTD” , FORMAT( [EBITDA % YTD] - [Bud EBITDA % YTD] , “0.0%” ) ,

DAX 3)
CY =
SWITCH( TRUE() ,
[Table Data] = “Actual” && [Period Table Data] = “MTD” , [CY MTD] ,
[Table Data] = “Actual” && [Period Table Data] = “YTD” , [CY YTD] ,
[Table Data] = “% TORE” && [Period Table Data] = “MTD” , FORMAT([% TORE MTD] , “0.0%” ),
[Table Data] = “% TORE” && [Period Table Data] = “YTD” , FORMAT ([% TORE YTD] , “0.0%” ))

As can be seen there is a lot of mentions of “Period Table Data” etc. Is there a way to optimise the formula?

``````CY KPI =
VAR CurrentItem =
TRIM ( SELECTEDVALUE ( 'Exec Summary'[KPI] ) )
VAR PeriodTableYTD = [Period Table Data] = "YTD"
VAR PeriodTableMTD = [Period Table Data] = "MTD"
VAR IsCurrentItemTR = CurrentItem = "Total Revenues"
VAR IsCurrentItemEBTIDA = CurrentItem = "EBITDA"
VAR IsCurrentItemEBTIDAPerc = CurrentItem = "EBITDA %"
RETURN
SWITCH (
TRUE (),
PeriodTableMTD && IsCurrentItemTR, [TORE MTD],
PeriodTableMTD && IsCurrentItemEBTIDA, [EBITDA MTD],
PeriodTableMTD && IsCurrentItemEBTIDAPerc, FORMAT ( [EBITDA % MTD], "0.0%" ),
PeriodTableYTD && IsCurrentItemTR, [TORE YTD],
PeriodTableYTD && IsCurrentItemEBTIDA, [EBITDA YTD],
PeriodTableYTD && IsCurrentItemEBTIDAPerc, FORMAT ( [EBITDA % YTD], "0.0%" )
)
``````

.

``````v Bud =
VAR ExecVar = [Exec Var Table Data] = "Var."
VAR ExecVarPerc = [Exec Var Table Data] = "% Var."
VAR EBITDA = [Exec Table Data] = "EBITDA %"
VAR PeriodTableYTD = [Period Table Data] = "YTD"
VAR PeriodTableMTD = [Period Table Data] = "MTD"
VAR FormattedYTD =
FORMAT ( [EBITDA % YTD] - [Bud EBITDA % YTD], "0.0%" )
VAR FormattedMTD =
FORMAT ( [EBITDA % MTD] - [Bud EBITDA % MTD], "0.0%" )
RETURN
SWITCH (
TRUE (),
ExecVar && EBITDA
&& PeriodTableMTD, FormattedMTD,
ExecVar && EBITDA
&& PeriodTableYTD, FormattedYTD,
ExecVarPerc && EBITDA
&& PeriodTableMTD, FormattedMTD,
ExecVarPerc && EBITDA
&& PeriodTableYTD, FormattedYTD
)
``````

.

``````CY =
VAR Actuals = [Table Data] = "Actual"
VAR PercTore = [Table Data] = "% TORE"
VAR PeriodTableMTD = [Period Table Data] = "MTD"
VAR PeriodTableYTD = [Period Table Data] = "YTD"
RETURN
SWITCH (
TRUE (),
Actuals && PeriodTableMTD, [CY MTD],
Actuals && PeriodTableYTD, [CY YTD],
PercTore && PeriodTableMTD, FORMAT ( [% TORE MTD], "0.0%" ),
PercTore && PeriodTableYTD, FORMAT ( [% TORE YTD], "0.0%" )
)``````