Hi guys
Has anyone any further tips on how to optimise DAX code below:
I’m writing a Management accounting package using ProfitBase’s Financial Reporting Matrix which is just fantastic.
Refresh times compared with MS tables are slower but the end presentation is just wicked.
Refresh times for code below with MS table is 9 secs and with PB Matrix 13-15s. I have contacted Profitbase about the differrence between the two. But I feel there is more optimisation required.
The reason for so many switch lines is due to differing calculations/Kpis required on different rows, which I don’t think I can reduce any further.
The end solution is in production, but speed for users is still an issue.
I have posted on this topic before and tried Sam’s financial reporting techniques but always come back to this switch statment as the faster option. There are no two way relationships and the tables are all one to many, cascading away from the fact table (thanks Sam! - that helped ages ago)
Brian J, if you’re out there I know you love a challenge!!
Thanks everyone
Pete
LineNumber =
VAR vMASum = [MASum]
VAR vMAKPI = [MACalc]
VAR vSelected_DivideBy = [Selected_DivideBy]
VAR vKpiDivide = DIVIDE( [MACalc1], [MACalc2], BLANK() )
VAR vMonthCount = DISTINCTCOUNT( 'Calendar'[MonthYear] )
VAR vDaysinMonth = count( 'Calendar'[Date] )
VAR vFixedCosts = CALCULATE(
[SumAmount],
Filter( all( MAReports ), MAReports[CostType] = "Fixed" ),
OperationCodes[OpCode03] = "Vehicle Sales"
)
VAR vVariableCosts = CALCULATE(
[SumAmount],
Filter( all( MAReports ), MAReports[CostType] = "Variable" ),
OperationCodes[OpCode03] = "Vehicle Sales"
) Return SWITCH(
vMAKPI,
//max( MAReports[MACalcType] )
Blank(),
vMASum,
"PU", vKpiDivide * vSelected_DivideBy,
"Perc", vKpiDivide * 100,
"PercOpCode03", CALCULATE(
vKpiDivide * 100,
FILTER(
ALLSELECTED( OperationCodes ),
OperationCodes[OpCode03] = MAX( MAReports[MACalc06] )
)
),
"PercExclMotab", DIVIDE(
CALCULATE( [MACalc1], OperationCodes[DeptCode] <> 15 ),
CALCULATE( [MACalc2], OperationCodes[DeptCode] <> 15 ),
Blank()
) * 100,
"PU100", vKpiDivide * 100 * vSelected_DivideBy,
"PUVehSales", CALCULATE(
vKpiDivide * vSelected_DivideBy,
OperationCodes[OpCode03] = "Vehicle Sales"
),
"PUVehSalesXRef", CALCULATE(
DIVIDE( [MACalc1], [MACalc2], 0 ) * vSelected_DivideBy,
OperationCodes[OpCode03] = "Vehicle Sales"
),
"AVG", Divide( [MACalc1], vMonthCount ),
"WorkDays", sum( 'Calendar'[WorkingDay] ),
"PerTech", DIVIDE( [MACalc1], Divide( [MACalc2], vMonthCount ) ),
"AVG2", Divide( vMASum, vMonthCount ),
"AnnPU", vKpiDivide * 12,
"BM", ( Divide( [MACalc1] -( [MACalc2] - [MACalc3] ), [MACalc1], BLANK() ) ) * 100,
"DaysInMonth", vDaysinMonth,
"PerDay", DIVIDE( [MACalc1], vDaysinMonth, blank() ),
"PerWeek", DIVIDE( [MACalc1], vDaysinMonth, blank() ) * 7,
"XRef", [MACalc1],
"XRefExclMotab", Calculate( [MACalc1], OperationCodes[DeptCode] <> 15 ),
"CFlow", CALCULATE( [MACalc1], MAFact[BF] = 0 ),
"CFlowTotal", [MACalc1] + CALCULATE( [MACalc2], MAFact[BF] = 0 ) + [MACalc3] + [MACalc4],
"Fixed", If(
vFixedCosts = 0,
0,
( vFixedCosts - [MACalc2] * Divide( [MACalc3], [MACalc4], 0 ) ) * -1
),
"Variable", If(
vVariableCosts = 0,
0,
( vVariableCosts + [MACalc2] * Divide( [MACalc3], [MACalc4], 0 ) ) * -1
),
"FixedPU", DIVIDE(
( ( vFixedCosts - [MACalc2] * Divide( [MACalc3], [MACalc4], 0 ) ) * -1 ),
[MACalc5],
Blank()
),
"VariablePU", DIVIDE(
( ( vVariableCosts + [MACalc2] * Divide( [MACalc3], [MACalc4], 0 ) ) * -1 ),
[MACalc5],
Blank()
),
// Bespoke JG to FILTER out items which are common across ALL depts//
"FilterDepts", CALCULATE(
[MACalc1],
FILTER( All( OperationCodes ), OperationCodes[OpCode01] = "Dept Profits" )
),
"FilterAdmin", CALCULATE(
[MACalc1],
FILTER( All( OperationCodes ), OperationCodes[OpCode01] = "Overheads" )
) + [MACalc2],
"OpProfit", CALCULATE(
[MACalc1],
FILTER(
All( OperationCodes ),
OperationCodes[ProfitCat02] = "Operating Profit"
)
),
"FilterExceptional", CALCULATE(
[MACalc1],
FILTER(
All( OperationCodes ),
OperationCodes[ProfitCat02] = "Exceptional Items"
)
),
"FilterSiteContribution", CALCULATE(
[MACalc1],
FILTER(
All( OperationCodes ),
OperationCodes[ProfitCat02] = "Operating Profit"
)
) - [MACalc2] + CALCULATE(
[MACalc2],
FILTER( All( OperationCodes ), OperationCodes[OpCode03] = "Central Costs" )
),
"CentralCosts", [MACalc1] + CALCULATE(
[MACalc2],
FILTER( All( OperationCodes ), OperationCodes[OpCode03] = "Central Costs" )
),
"CentralCosts", IF(
HASONEVALUE( SiteCodes[Dealership] ),
[MACalc1],
CALCULATE( [MACalc1], SiteCodes[SiteCode] <> 90 )
),
"FilterOpcode02", CALCULATE(
[MACalc1],
FILTER(
ALLSELECTED( OperationCodes ),
OperationCodes[OpCode02] = MAX( MAReports[MACalc06] )
)
),
"FilterOpcode03", CALCULATE(
[MACalc1],
FILTER(
ALLSELECTED( OperationCodes ),
OperationCodes[OpCode03] = MAX( MAReports[MACalc06] )
)
),
// Bespoke JG for parts stats//
"FilterDeptCode", calculate(
[MACalc1],
FILTER(
All( OperationCodes ),
OperationCodes[DeptCode] = max( MAReports[MACalc02] )
)
),
//"FilterDeptCategory" , calculate( [MACalc1] , FILTER( All( OperationCodes ) , OperationCodes[MADeptCategory] = max( MAReports[MACalc06] ) ) ) ,
"FilterShowroom", calculate(
[MACalc1],
FILTER( All( OperationCodes ), OperationCodes[MADeptCategory] = "Showroom" )
),
"FilterBusCentre", calculate(
[MACalc1],
FILTER( All( OperationCodes ), OperationCodes[DeptCategory] = "Bus Centre" )
),
"Filter3132", calculate(
[MACalc1],
FILTER(
All( OperationCodes ),
OperationCodes[DeptCode] = 31 ||
OperationCodes[DeptCode] = 32
)
),
"Filter3141", calculate( [MACalc1], FILTER( All( OperationCodes ), OperationCodes[DeptCode] <> 32 ) ),
"PartsHoursPU", DIvide(
[MACalc1],
calculate(
[MACalc2],
FILTER(
All( OperationCodes ),
OperationCodes[DeptCode] = max( MAReports[MACalc03] )
)
)
),
//"CarExtHoursPU" , DIvide( [MAArg1] , calculate( [MAArg2] , FILTER( All( OperationCodes ) , OperationCodes[DeptCode] = 31 ) ) ) ,
//"CVExtHoursPU" , DIvide( [MAArg1] , calculate( [MAArg2] , FILTER( All( OperationCodes ) , OperationCodes[DeptCode] = 32 ) ) ) ,
//"BodyExtHoursPU" , DIvide( [MAArg1] , calculate( [MAArg2] , FILTER( All( OperationCodes ) , OperationCodes[DeptCode] = 41 ) ) ) ,
"CarHoursPU", DIvide(
[MACalc1],
calculate( [MACalc2], FILTER( All( OperationCodes ), OperationCodes[DeptCode] <> 32 ) )
),
//"CVHoursPU" , DIvide( [MAArg1] , calculate( [MAArg2] , FILTER( All( OperationCodes ) , OperationCodes[DeptCode] = 32 ) ) ) ,
"OpeningEquity", CALCULATE( [MACalc1], MAFact[BF] = 1 ),
"ClosingEquity", vMASum + [MACalc1],
"Space", 0.001
) * max( MAReports[Signage] ) / If( max( MAReports[MAThousands] ) = 1, vSelected_DivideBy, 1 )