Formula execution results in Excel crash while DAX Studio works fine

razmochaev — Thu, 14 May 2020 14:28:29 GMT

Hi everyone.

I ran into the problem and I think it's because of imperfect formula.

I need to compute costs attributable to projects only for projects that had sales in a given period. Also, the costs should be accumulated between period of current sale and previos sale.

The data model is in Russian, so I give the translation:

Проекты = Projects

Календарь[Дата] = Calendar[Date]

Продажи = Sales

[Выручка] = [SalesAmount]

Себестоимость = Cost Of Services Rendered

The DAX code I've come up with:

EVALUATE VAR ProjectsSalesDates = FILTER ( ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Проекты'[id], 'Календарь'[Дата] ), "Продажи", [Выручка] ), NOT ( ISBLANK ( 'Проекты'[id] ) ) && [Продажи] > 0 ) VAR ProjSalesPrevDates = ADDCOLUMNS ( ProjectsSalesDates, "PrevSalesDate", MAXX ( ( VAR CurrProject = [id] VAR CurrDate = [Дата] VAR ProjectDates = FILTER ( ProjectsSalesDates, [id] = CurrProject && [Дата] < CurrDate ) RETURN ProjectDates ), [Дата] ) ) VAR ProjectsCosts = ADDCOLUMNS ( ProjSalesPrevDates, "Себестоимость трудозатрат", CALCULATE ( [2.2. Стоимость трудозатрат], ( VAR CurrDate = [Дата] VAR PrevDate = [PrevSalesDate] VAR CostPeriod = DATESBETWEEN ( 'Календарь'[Дата], PrevDate, CurrDate ) RETURN CostPeriod ) ), "Себестоимость прямых затрат", CALCULATE ( [Прямые затраты], ( VAR CurrDate = [Дата] VAR PrevDate = [PrevSalesDate] VAR CostPeriod = DATESBETWEEN ( 'Календарь'[Дата], PrevDate, CurrDate ) RETURN CostPeriod ) ) ) VAR Result = ADDCOLUMNS ( ProjectsCosts, "Себестоимость", [Себестоимость трудозатрат] + [Себестоимость прямых затрат] ) RETURN SUMX ( Result, [Себестоимость])

In DAX Studio is works fine and returns the following table:

It returns 529 rows and execution time is 3250 ms.

But when I input this formula in Excel, it crashes :-(.

If anyone could give me a hint, whether it's due to bad formula of something else, I'd be very grateful!

Re: Formula execution results in Excel crash while DAX Studio works fine

razmochaev — Fri, 15 May 2020 09:15:33 GMT

I also tested it in PowerBI and it works pretty well, but in different versions of Excel it results in a crash.

Re: Formula execution results in Excel crash while DAX Studio works fine

Anonymous — Fri, 15 May 2020 09:57:12 GMT

First off, you should never use SUMMARIZECOLUMNS in a measure since this function does not respect context transition. Then, the measure (is it a measure or just a query?) seems to me to be a bit too complex with calculations that are materialized, probably unnecessary, thus making the calculations slower than they could be.

Secondly, what's the exact error message you get?

Best
D

Re: Formula execution results in Excel crash while DAX Studio works fine

razmochaev — Wed, 20 May 2020 21:35:48 GMT

Thank you for the remarks! I've made some improvements in the formula and now it doesn't crash. Still, I didn't get the result I wanted, so I guess this topic must be closed.

topic Re: Formula execution results in Excel crash while DAX Studio works fine in DAX Commands and Tips

Formula execution results in Excel crash while DAX Studio works fine

Re: Formula execution results in Excel crash while DAX Studio works fine

Re: Formula execution results in Excel crash while DAX Studio works fine

Re: Formula execution results in Excel crash while DAX Studio works fine