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
