How slow is creating measures and columns in your model?

[joerykeizer]

Hi everyone,

Over time I have created a Power BI model that has become quite extensive. Refreshing and filtering could be faster but it's acceptable. What is getting annoying is the time it takes to create/edit/delete columns and measures. I just now timed this and each of these actions take almost 2 minutes individually. This slows down development a lot.

My model size is just under 500MB and I have 96 active queries. Most of them have relationships and are somehow connected. I use a laptop with an i5-6300 HQ CPU and 32 GB memory. Allmost all queries I use are preset SQL statements without any further applied steps in the query editor. Most of them are fast. I do have a lot of calculated columns and measures in the model. Could that be slowing everything down or is it just inevitable for a model this 'complex'?

I seems likely that others have similar models so I would assume you have similar problems? Thought it would be interesting to read about some other cases. It's not just me right?

[roman_gorbunov]

my model is about 300mb, 400 queries (every include 15-20-30 columns), any changing or creating of column, measure, link takes about 3-4-5 minites! awful...

[Anonymous]

Same here, not as slow as yours (also much smaller model - ~90MB), but every time you want to create a measure, apply measure changes, change formating etc.. it takes areound 10-20 seconds, which is super annoying given how many clicks are involved.

Did you manage to resolve this? 

Thanks

[jsh121988]

I had issues when I had a granular date table with 7 million rows relating to a table with 1.5 million rows. I simplified my related columns to date instead of datetime, and reduced my date table to days only.

So try identifying tables that could have simpler relations, or perform a table join instead of relationships.

With big data and measures, try avoiding filter within calculate, and instead create another calculated column that returns value or zero where filter criteria met. Then do a sum(column) in your measure. This has taken my measures from 15 second calc to less than 1 second on 1.5 million rows.

[v-jiascu-msft]

@joerykeizer

Hi,

I am afraid it’s a truth that the Power BI will slow down if you have such a big model. I have had a test. My model is 327MB, 10 tables and very simple. It took almost 1 minute to add a column. I found the speed depends on the size of the table in some way. A table with 12 million rows is slow while a table with hundreds rows is very fast in the same model. May you should optimize you model. Please reference this article.

http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

Best Regards!

Dale