DAX - Switch Optimisation

[Pete673]

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 )

[Melissa]

Hi @Pete673,

You have quite a few FILTER/ALL statements where you pass the entire table to ALL as first argument of FILTER, while you only have a single predicate. So start by changing those from:

FILTER( ALL( MAReports ), MAReports[CostType] = "Fixed" )

to

FILTER( ALL( MAReports[CostType]), MAReports[CostType] = "Fixed" )

[AntrikshSharma]

@Pete673

1. Do what Melissa said

2. CALCULATE can’t change filter context of variable evaluated outside of it, so OperationCodes… = “Vehicle Sales” has no impact on the first argument of CALCULATE, you will have to replace variables with original measures

   =
   CALCULATE (
   vKpiDivide * vSelected_DivideBy,
   OperationCodes[OpCode03] = “Vehicle Sales”
   )

3. If a measure isn’t used more than once then don’t store it outside SWITCH as it will be evaluated irrespective of if you call it inside SWITCH or not, and that could worsen the performance

[Pete673]

Thanks @Melissa and @AntrikshSharma.

Flowed those tips through but didn’t really impact anything. Latest Dax is below
Melissa’s tip I understand but the tables are small look up tables ( less than 200 rows).
Good advice though.
Antriksh advice actually slowed things up but was worth a shot. I have read about filter context in variables and return statements before and the end returned numbers are correct!!

Latest DAX below for comment by anybody who can help.

I have done some research on this before and have given up a number of times. Speeding this up is a tough call because all SWITCH items are evaluated even though not required.
So does anybody know a way of preventing this happening?

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[CostType] ), MAReports[CostType] = "Fixed" ),
    OperationCodes[OpCode03] = "Vehicle Sales"
) 
VAR vVariableCosts = CALCULATE(
    [SumAmount],
    Filter( all( MAReports[CostType] ), MAReports[CostType] = "Variable" ),
    OperationCodes[OpCode03] = "Vehicle Sales"
) Return SWITCH(
    [MACalc],
    //max( MAReports[MACalcType] )
    BLANK(),
    vMASum,
    "Perc", vKpiDivide * 100,
    "PercOpCode03", CALCULATE(
        vKpiDivide * 100,
        FILTER(
            ALLSELECTED( OperationCodes[OpCode03] ),
            OperationCodes[OpCode03] = MAX( MAReports[MACalc06] )
        )
    ),
    "PercExclMotab", DIVIDE(
        CALCULATE( [MACalc1], OperationCodes[DeptCode] <> 15 ),
        CALCULATE( [MACalc2], OperationCodes[DeptCode] <> 15 ),
        Blank()
    ) * 100,
    "PU", vKpiDivide * vSelected_DivideBy,
    "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[OpCode01] ),
            OperationCodes[OpCode01] = "Dept Profits"
        )
    ),
    "FilterAdmin", CALCULATE(
        [MACalc1],
        FILTER(
            All( OperationCodes[OpCode01] ),
            OperationCodes[OpCode01] = "Overheads"
        )
    ) + [MACalc2],
    "OpProfit", CALCULATE(
        [MACalc1],
        FILTER(
            All( OperationCodes[ProfitCat02] ),
            OperationCodes[ProfitCat02] = "Operating Profit"
        )
    ),
    "FilterExceptional", CALCULATE(
        [MACalc1],
        FILTER(
            All( OperationCodes[ProfitCat02] ),
            OperationCodes[ProfitCat02] = "Exceptional Items"
        )
    ),
    "FilterSiteContribution", CALCULATE(
        [MACalc1],
        FILTER(
            All( OperationCodes[ProfitCat02] ),
            OperationCodes[ProfitCat02] = "Operating Profit"
        )
    ) - [MACalc2] + CALCULATE(
        [MACalc2],
        FILTER(
            All( OperationCodes[OpCode03] ),
            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[OpCode02] ),
            OperationCodes[OpCode02] = MAX( MAReports[MACalc06] )
        )
    ),
    "FilterOpcode03", CALCULATE(
        [MACalc1],
        FILTER(
            ALLSELECTED( OperationCodes[OpCode03] ),
            OperationCodes[OpCode03] = MAX( MAReports[MACalc06] )
        )
    ),
    // Bespoke JG for parts stats//
    "FilterDeptCode", calculate(
        [MACalc1],
        FILTER(
            All( OperationCodes[DeptCode] ),
            OperationCodes[DeptCode] = max( MAReports[MACalc02] )
        )
    ),
    "FilterShowroom", calculate(
        [MACalc1],
        FILTER(
            All( OperationCodes[MADeptCategory] ),
            OperationCodes[MADeptCategory] = "Showroom"
        )
    ),
    "FilterBusCentre", calculate(
        [MACalc1],
        FILTER(
            All( OperationCodes[DeptCategory] ),
            OperationCodes[DeptCategory] = "Bus Centre"
        )
    ),
    "Filter3132", calculate(
        [MACalc1],
        FILTER(
            All( OperationCodes[DeptCode] ),
            OperationCodes[DeptCode] = 31 ||
            OperationCodes[DeptCode] = 32
        )
    ),
    "Filter3141", calculate(
        [MACalc1],
        FILTER( All( OperationCodes[DeptCode] ), OperationCodes[DeptCode] <> 32 )
    ),
    "PartsHoursPU", DIvide(
        [MACalc1],
        calculate(
            [MACalc2],
            FILTER(
                All( OperationCodes[DeptCode] ),
                OperationCodes[DeptCode] = max( MAReports[MACalc03] )
            )
        )
    ),
    "CarHoursPU", DIvide(
        [MACalc1],
        calculate(
            [MACalc2],
            FILTER( All( OperationCodes[DeptCode] ), 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 )

[ankit]

Hi @Pete673

I can’t pinpoint exactly what can be tried but suggests going through below articles if not already done. These provide good inputs on what can be done to achieve better performance using Switch, Filter, Calculate etc. Then it will be matter of trying things out.

Optimizing IF and SWITCH expressions using variables - SQLBI

Optimizing DAX expressions involving multiple measures - SQLBI

Filter Arguments in CALCULATE - SQLBI

Avoid using FILTER as a filter argument in DAX - DAX

Analyzing the performance of DISTINCTCOUNT in DAX - SQLBI

Thanks
Ankit J

[Pete673]

Hi Ankit
There are a couple of links there that I haven’t read but most of it I’ve read and re-read a hundred times.

Whilst the technical issue of the evaluation of all Switch lines seems complete nonsense to me, I guess there must be something under the bonnet of vertipaq that causes/forces this to happen. From a tech perspective I guess we soldier on until Microsoft sort it.

As regards my own issue with the DAX in this post, I did a bit of debugging and discovered:

1. the formulae was being evaluated 3 times because of measure branching rather than once and a single filter expression was really slowing it up.
2. went through a long exercise in SQL Server to rationalise and reduce the data being fed to the model.
   This enabled me to get rid of most the switch lines with filter expressions

There is possibly still a to do bit more but I’ve got the speed on most pages down from 13-15 seconds on my PBI Desktop to 3 seconds. Even my slowest page is refreshing in 9 seconds. We’ll see what happens in the service later in the afternoon.

So thank you to everyone who has contributed to this post.

Pete

[AntrikshSharma]

Check for CALLBACKDATAID in the queries generated and see where it appears, you will have to optimize it accordingly, if there are a lot of CALLBACKDATAIDs then the performance will take a hit, basically what happens is that Storage Engine can do basic calculations such as SUM/DIVIDE but for more complex calculations Formula Engine has to be used and it can only use 1 CPU core while storage engine can use as many CPU cores available, and maybe since you are using so many measures inside SWITCH the performance is taking a toll.