Formula stuck on "working on it"

[ldamato]

Hi all, 

I have the following formula:

When I return either "_pct975" or "_pct25", it works as expected, but as soon as I add these variables to the calculation of the variable "mean", Power BI Desktop gets stuck on "working on it" forever, and the "Microsoft SQL Server Analysis Services" process goes to the roof in task manager.

Am I doing something that I shouldn't in the formula? 

FYI, "VALUES_NA" table has around 1.4 million rows and is loaded from sql server (import)

Thanks a lot

[v-rzhou-msft]

Hi @ldamato ,

I think your issue may be caused by too large size data, complex data model(relationship), complex dax code and too much or complex visualizations.

For too large size data, I suggest you to summarize your table or you can try Direct Query connection mode to get data from SQL Server.

For reference:

Data reduction techniques for Import modeling - Power BI | Microsoft Learn

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

I suggest you to refer to below offical blogs to check how to optmize data model, optmize dax code and visualizations.

For reference: Optimization guide for Power BI - Power BI | Microsoft Learn

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.