cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkPBI
Frequent Visitor

Working on larger .pbix - best practices for development speed

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?

1 ACCEPTED SOLUTION
Watsky
Super User
Super User

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? 

Watsky_0-1664891141647.png

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.

Watsky_1-1664893655086.png

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.

 Watsky_3-1664892678755.png

 

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

Watsky_1-1664891951424.png

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

Watsky_0-1664891906834.png

Watsky_0-1664892882289.png

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.

Watsky_0-1664893399924.png


Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

View solution in original post

1 REPLY 1
Watsky
Super User
Super User

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? 

Watsky_0-1664891141647.png

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.

Watsky_1-1664893655086.png

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.

 Watsky_3-1664892678755.png

 

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

Watsky_1-1664891951424.png

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

Watsky_0-1664891906834.png

Watsky_0-1664892882289.png

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.

Watsky_0-1664893399924.png


Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.