How can I improve this calculatetable DAX measure performance?

[Okazakipedro]

Dear all, good morning.

I have a measure I would like to use in my reports but this is taking extremly long to show the visuals. I have a table called Support Category Table and this would have the categories A,B,C and its lower and upper boundaries, thad define in which category a customer should fall into, based on its Revenue or Profit (this specific measure is for profit but I amd oing the same for revenue)

Currently, this would be my measure:

category_abcByCumulativeProfit_Profit =

VAR QuartersToAnalyze = CALCULATE(DISTINCTCOUNT('Calendar'[Quarter & Year]),FILTER(

        ALLSELECTED(Fact_JobFinancials),Fact_JobFinancials[CalendarDate] <= MAX(Fact_JobFinancials[CalendarDate]

    )))

VAR SalesByProduct =

    CALCULATETABLE (

        ADDCOLUMNS (

            SUMMARIZE ( Fact_JobFinancials, Fact_JobFinancials[Controlling Customer Code] ),

            "@ProdSales", [job_cumulative_totalProfitUSD/EUR]

        ),

        ALLSELECTED ( Fact_JobFinancials )

    )

VAR AllSales =

    CALCULATE (

        [job_cumulative_totalProfitUSD/EUR],

        ALLSELECTED ( Fact_JobFinancials )

    )

VAR ProductsInClass =

    FILTER (

        CROSSJOIN (

            SalesByProduct,

            'Support_CategoryTable'

        ),

            ([job_cumulative_totalProfitUSD/EUR] > 'Support_CategoryTable'[ProfitLowerBoundaries]*QuartersToAnalyze

            && [job_cumulative_totalProfitUSD/EUR] <= 'Support_CategoryTable'[ProfitUpperBoundaries]*QuartersToAnalyze)

        )

VAR Result =

    CALCULATE (

        [job_cumulative_totalProfitUSD/EUR],

        KEEPFILTERS ( ProductsInClass )

    )

RETURN

    Result


But it takes too long if I try to have this together with the cartegory column where my boundaries are defined... 

Anyone have a better solution?

Thank you and Best Regards,

Pedro Okazaki

[freginier · Accepted solution]

Hey there!

Your DAX measure performance issue comes from using CALCULATETABLE, CROSSJOIN, and FILTER extensively, which can be resource-intensive. Here's a solution for you:

Instead of CROSSJOIN(SalesByProduct, 'Support_CategoryTable'), which generates all possible combinations, use a direct lookup.

category_abcByCumulativeProfit_Profit =
VAR CY_Quarters =
CALCULATE(
DISTINCTCOUNT('Calendar'[Quarter & Year]),
ALLSELECTED(Fact_JobFinancials),
Fact_JobFinancials[CalendarDate] <= MAX(Fact_JobFinancials[CalendarDate])
)

VAR TotalProfit =
CALCULATE(
[job_cumulative_totalProfitUSD/EUR],
ALLSELECTED(Fact_JobFinancials)
)

VAR SelectedCategory =
LOOKUPVALUE(
'Support_CategoryTable'[Category],
'Support_CategoryTable'[ProfitLowerBoundaries], "<=", TotalProfit / CY_Quarters,
'Support_CategoryTable'[ProfitUpperBoundaries], ">=", TotalProfit / CY_Quarters
)

RETURN
SelectedCategory

Hopefully this will make faster performance as unnecessary row-by-row filtering is eliminated and accurate category assignment based on cumulative profit and support category table.

Hope this helps!

😁😁