cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sandeep_me
Helper I
Helper I

Optimising a DAX

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
 
3 REPLIES 3
littlemojopuppy
Super User
Super User

@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
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Daryl-Lynch-Bzy
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors