Answered by:
Slow Performance of DAX Query
Question

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 (23 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'))
ThanksWords offer the means to meaning, and for those who will listen, the enunciation of truth  V for Vendetta.
Thursday, May 24, 2012 12:12 AM
Answers

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 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, October 28, 2013 6:54 AM
Tuesday, June 5, 2012 10:26 PM
All replies

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.comMonday, May 28, 2012 10:33 PM 
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.
Tuesday, May 29, 2012 4:49 PM 
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 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, October 28, 2013 6:54 AM
Tuesday, June 5, 2012 10:26 PM