Slow Performance of DAX Query

# Slow Performance of DAX Query

• jeudi 24 mai 2012 00:12

I have the following Measures in Powerpivot. In the first measure, I am getting the max of the quantity. In second measure, I am calculating the last non blank value for each product and summing up the values. In the third measure I am getting the running total with help of first two measures. However this is very slow and is impacting performance even for less number of records (2-3 million). Any suggestions on how can I improve the performance. Thanks in advance.

M1:=MAX('Fact'[Quantity])

M2:=SUMX(VALUES('Fact'[ProductKeyID]),CALCULATE([M1],LASTNONBLANK('Fact'[Date],[ M1])))

M3:=CALCULATE([M2],DATESBETWEEN('Dates'[FullDate],BLANK(),LASTDATE('Dates'[FullDate])),ALL('Dates'))

Thanks

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

### Toutes les réponses

• lundi 28 mai 2012 22:33

San,

That would be nice to test, can you share the data by sending me the workbook? I am currently thinking at some alternatives, but I would really need the data to avoid any mistake.

Thanks

Alberto Ferrari
http://www.powerpivotworkshop.com

• mardi 29 mai 2012 16:49

Alberto...i have uploaded the excel book in the following html   http://sdrv.ms/JD69eA
I found out the problem with my code. It is in Measure M2. The problem is with ProductKeyID. I have over 90 million rows in productkeyid table in production. As far as i understand, SUMX is just like a for loop and it is doing calculation for each productkeyid and for 90 million rows in the table. Hence the data is very slow. The idea is to calculate nested aggregation across  product to find  qty based on last inventory taken for period up through current context. The quant is always either 0 or 1 as seen in the excel book. I have also another variation of the formula as below, which is taken for Data analysis express for powerpivot excel 2010 white paper.
SUMX(VALUES(DimProduct[ProductKey]), CALCULATE(SUM(FactInventory[OnHandQuantity]),  LASTNONBLANK(DATESBETWEEN(DimDate[Datekey],BLANK(),MAX(DimDate[Datekey])),CALCULATE(SUM(FactInventory[OnHandQuantity])))))
In both variations, I am getting slow results and i am pretty sure, it has to do with ProductKeyId being huge data. I thank you for your help.

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

• mardi 5 juin 2012 22:26

Thanks for the repro. I tried to play with the formulas, there are several variations that can be used but, to get some results I would need to play with the full dataset. I agree that using SUMX over 90 millions rows for each iteration is a pain, I have tried several versions with SUMMARIZE, but it is hard to say if one is better than the other, since wth few data they all perform fast...

Alberto Ferrari
http://www.powerpivotworkshop.com