Working on larger .pbix - best practices for development speed

[MarkPBI]

Does anyone have any best practices or tips to share for when you're working on larger .pbix files? I have several projects that become cumbersome to work on as the number of visuals, measures, and datasets, etc. increases. 

Almost every single action you take leads to a loader... Adding a visual leads to a "waiting" loader, adding a new measure leads to a loader, renaming or editing things leads to a loader. It's not that the loaders last super long, but with every action that you take you're waiting a few seconds, which becomes frustrating.

Anyone have any tips to minimize / optimize this?

[Watsky · Accepted solution]

Hi @MarkPBI,

First off. How large is your pbix file at this point? How many visuals are on screen per page? What types of visuals are you using (can you screenshot your page)? I'd be curious to see how many visuals you have and of those, how many are slicers?

Have you turned off Auto date/time? 

If not this will make a big impact if you happen to have more than one date field. You'd want to turn this off in your options under Data Load for Current File at a minimum but truthfully you may want to turn this off globally. With this on Power BI creates a date table for every date field you have so that you have a hierarchy for each date field. This is a nice tool when you have a small dataset but when you have a large dataset with mutliple date fields you end up with more tables in your model causing your model to bloat.

While you're in there I'd also turn off Allow data previews to download in the background. 

Another trick you can do is look at your data and find a field that can be used to filter the data down like a date field or some sort of dimensional information (like color, age, type, source) create a parameter in Power Query (Transform Data) using the value from the field and filter it down in Power Query. Then once you've published you can remove the parameter set from the service. That way you can still develop using a subset of the data and not be limited to just that filtered down data. Make sure to uncheck required and to set type to something other than Any so that you can remove the parameter.

 

Then filter the field where it's equal to the parameter

Then after you've published you can remove the parameter, and refresh the data source again.

The last recommendation I have for you is to take a look at the Performance Analyzer. This is found under the View Tab. Refresh all the visuals and sort the duration descending by DAX query. This will show you the longest running DAX queries you have from longest to shortest. Anything taking longer than 5 seconds you should look into finding ways to optimize the DAX. Also pay attention to the Other section. This tells you the length of time the visual took waiting for another visual to finish. If the other section is the longest section this tells you that you have too many visuals and a reduction of visuals would help reduce the load time.