Optimising a DAX

[sandeep_me]

Hello Community, 

I have DAX here which is needed some optimisation. In the sense at the moment whhen I'm using this DAX in visualisation it's taking a lot of time/memory to load. Any suggestions on how I can optimise? 

Price Change % Month-Last Year Customer =

VAR table1 =

CALCULATETABLE (

ADDCOLUMNS (

SUMMARIZE ( fact_CustInvoice, fact_CustInvoice[id_dim_Customer], fact_CustInvoice[id_dim_Item],fact_CustInvoice[UniqueKey]),

"SalesQty", [Sales Qty],

"SalesQtyLY", [Sales Qty LY],

"Sales_", [Sales],

"SalesLY", [Sales LY]

)

)

VAR table2 =

ADDCOLUMNS (

table1,

"Include",

IF (

ISBLANK ( [SalesQty] ) || ISBLANK ( [SalesQtyLY] )

|| ISBLANK ( [SalesLY] )

|| ISBLANK ( [Sales_] )

|| [SalesQty] <= 0

|| [SalesQtyLY] <= 0

|| [SalesLy] <= 0

|| [Sales_] <= 0,

FALSE,

TRUE

)

)

VAR table3 =

ADDCOLUMNS (

table2,

"AvgPrice", IF ( [Include] = TRUE, DIVIDE ( [Sales_], [SalesQty] ), 0 ),

"AvgPriceLY", IF ( [Include] = TRUE, DIVIDE ( [SalesLY], [SalesQtyLy] ), 0 ),

"Result",

IF ( [Include] = TRUE, [Sales_],0)

)

VAR table4 =

SUMMARIZE(table3,[Include],fact_CustInvoice[UniqueKey],"distinctcount",IF([Include]=TRUE(),DISTINCTCOUNTNOBLANK(fact_CustInvoice[UniqueKey]),BLANK()))

VAR AveragePriceCurrentYear = SUMX ( FILTER ( table3, [Include] = TRUE ), [AvgPrice] )

VAR AveragePriceLastYear = SUMX ( FILTER ( table3, [Include] = TRUE ), [AvgPriceLY] )

VAR SalesForItemCustomerBothPeriods= SUMX ( FILTER ( table3, [Include] = TRUE ), [Result] )

VAR conditions=SUMX(table4,[distinctcount])

VAR PriceDev = IF(ISBLANK(DIVIDE(AveragePriceCurrentYear,AveragePriceLastYear)),BLANK(),DIVIDE(AveragePriceCurrentYear,AveragePriceLastYear)-1)

RETURN

IF (

conditions=1,IF(ISBLANK(DIVIDE(AveragePriceCurrentYear,AveragePriceLastYear)),BLANK(),DIVIDE(AveragePriceCurrentYear, AveragePriceLastYear)-1),

[Price Change Impact Month-Last Year Customer]/ SalesForItemCustomerBothPeriods

)

KR,

Sandeep

[littlemojopuppy]

@sandeep_me you are making extensive use of the SUMMARIZE function, which has performance issues.  I'd suggest rewriting to use some combination of GROUPBY and ADDCOLUMNS.  Hope this helps!

[mahoneypat]

There is a lot going on in that measure (many nested measures, IFs, etc.). Any sub-optimal pieces are then multiplied by the granularity of your first table variable. I would encourage you to watch one of the optimizing DAX videos by SQLBI and use DAX Studio to break it down (to see which parts are causing the issues and where you can reduce the granularity).

Pat

[Daryl-Lynch-Bzy]

Hi @sandeep_me , I am sorry, but this is bit too complex for this forum.  If performance is an issue, you may want to consider moving the logic back in Power Query.