topic Re: Any possibility to optimize below DAX query to use in SSAS Tabular Cube? in DAX Commands and Tips

Any possibility to optimize below DAX query to use in SSAS Tabular Cube?

KoppulaPSR — Sat, 22 Jun 2024 10:58:07 GMT

Dear Experts,

I would like to check, whether we can tune below 2 queries w.r.t perfomance improvment. Presently both queries are executing in ~80 seconds which is impacting my over all report rferesh cycle. Kinldy advise.

Below query i use as a part of Month Over Month Calulcation.

[12MonthTotalMoM] := CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-1,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-2,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-3,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-4,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-5,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-6,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-7,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-8,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-9,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-10,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-11,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-12,MONTH))

Below query i use as a part of Year Over Year Calulcation. Where i take previous year rolling 12 months.

[12MonthTotalYoY] := CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-12,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-13,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-14,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-15,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-16,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-17,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-18,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-19,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-20,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-21,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-22,MONTH))+ CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-23,MONTH))

Re: Any possibility to optimize below DAX query to use in SSAS Tabular Cube?

AntrikshSharma — Sat, 22 Jun 2024 11:42:44 GMT

KoppulaPSR In your date table create a column that will act as a contiguous Year Month Number since 1899-12-30,

The code for the column will be:

Dates[Year] * 12 + Dates[Month Number]

Then you just to filter last 12 Year Month Numbers using:

VAR ActiveYearMonthNumber = MAX ( Dates[Year Month Number] ) VAR Result = CALCULATE ( [Total Sales], Dates[Year Month Number] >= ActiveYearMonthNumber - 12 && Dates[Year Month Number] < ActiveYearMonthNumber, REMOVEFILTERS ( Dates ) ) RETURN Result

This calculation will be just like writing

CALCULATE ( [Total Sales], PARALLELPERIOD ( Dates[Date], -1, MONTH ) ) ... + CALCULATE ( [Total Sales], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

If you don't want grand total then you can add ISINSCOPE

IF ( ISINSCOPE ( Dates[Year] ), -- Whatever is the top level column in the visual VAR ActiveYearMonthNumber = MAX ( Dates[Year Month Number] ) VAR Result = CALCULATE ( [Total Sales], Dates[Year Month Number] >= ActiveYearMonthNumber - 12 && Dates[Year Month Number] < ActiveYearMonthNumber, REMOVEFILTERS ( Dates ) ) RETURN Result )

If you want additive sub totals and grand total then you can use SUMX.

SUMX ( VALUES ( Dates[Year Month Number] ), VAR ActiveYearMonthNumber = Dates[Year Month Number] VAR Result = CALCULATE ( [Total Sales], Dates[Year Month Number] >= ActiveYearMonthNumber - 12 && Dates[Year Month Number] < ActiveYearMonthNumber, REMOVEFILTERS ( Dates ) ) RETURN Result )

Re: Any possibility to optimize below DAX query to use in SSAS Tabular Cube?

KoppulaPSR — Sat, 22 Jun 2024 13:12:20 GMT

AntrikshSharma Your idea is brilliant, now i have used exited ID column in time dimension, which i created with Key Identity. Post impelmentation report refresh cycle reduced by 30%. Needs to tune further of another metrics as well.

Re: Any possibility to optimize below DAX query to use in SSAS Tabular Cube?

Anonymous — Mon, 24 Jun 2024 08:55:42 GMT

HI,KoppulaPSR
Can you tell me if your problem is solved? If yes, please accept it as solution.

Best Regards,

Leroy Lu