Improve Power BI Performance with Auto Date/Time and DAX Variables


By:   |   Updated: 2020-08-18   |   Comments   |   Related: 1 | 2 | 3 | More > Power BI


Problem

In part 1 of this series about Power BI optimization, I demonstrated how implementing Vertical Filtering and preference for custom columns created in Power Query could help improve performance of Power BI reports. In this second part of the series I will demonstrate how disabling "Auto date/time" and using more variables in DAX measures calculations could be used to optimize Power BI reports performance. I would encourage you to read Part 1 of this series to ensure you understand all discussed options available for performance optimization in Power BI.

Solution

This part of the series will focus on what issues can be resolved by "Auto date/time" and try using more variables in DAX measures calculations options as mentioned above.

Disable "Auto date/time" in data load options settings

The main purposes of the auto date/time are to support convenient time intelligence reporting based on the date columns loaded into the data model. I will not go into more detail as to what goes on behind the scene if auto date/time is enabled in Power BI desktop, you can read more on this here. However, I will be demonstrating how it might be affecting performance and a best practice on this.

I have imported the "SalesOrderHeader" table from AdventureWorks2014 database for the purpose of this demo. Note that the table has four datetime columns as shown in the diagram below.

Snapshot to show dates columns on table

So, when this model is saved to a file location, I can view the size of the model as seen below showing size as 1,721KB currently.

Snapshot showing initial model size

Usually, most model sizes are usually higher than this, thus it becomes necessary to reduce the model size for optimization purposes by disabling the "Auto date/time" in the data load options settings. We can configure this in either of two ways (Globally or Current File). If done globally, it would only affect new files imported to the model and, if on the other hand done on the current file it would affect the current files in the model. More information on Global and Current File can be researched here.

For this demo I have applied configuration using the Current File option as can be seen in the following diagrams. Click on "File".

Snapshot showing location of File tab in Power BI Desktop

Then select "Options and Settings" then "Options".

Snapshot showing navigation to Options settings in Power BI desktop

The next two diagrams show the Global option and the Current File Option for configuring the "Auto date/time" options.

Snapshot to show Global Data Load options for Time Intelligence
Snapshot to show Current File Data Load options for Time Intelligence

After unchecking the "Auto date/time" option in the Current File data load section, click "Ok". Then if you check, you can now see that the calendar symbol at the back of the DateTime columns in the SalesOrderHeader table have all vanished as seen in the diagram below.

Snapshot showing how datetime columns looks after deactivating it in Current Filer options settings

Then save your report as usual and go back to the location where the PBIX was saved earlier, it can now be seen that the model size has reduced from 1,721KB to about 1,697KB, a difference of about 24KB. Note that this could be higher depending on your model and this is even much more effective in larger models. See image below.

Snapshot showing final model size

Using more variables in DAX measures calculations

Among the importance of variables in Power BI data models include improved performance, reduced complexity, improved readability, and simplified debugging. However, this tip is solely looking at use of variables for improving performance only. You can get more information on other benefits of variables here.

To demonstrate how the use of variables can help optimize Power BI models (particularly the refresh time of visuals), I have again used the imported SalesOrderHeader table from AdventureWork2014. I have created same DAX calculated measure in two different ways, one with variables and the other without variables. Both measures are used to calculate Year on Year Growth in total Orders made. The calculated measures are as follows.

Snapshot showing DAX calculated measure without variables.
Orders YoY Growth (Without Variables) = 
DIVIDE (
    ( [Total Orders] - CALCULATE ( [Total Orders], PARALLELPERIOD ( DateTable[Date], -12, MONTH ) ) ),
    CALCULATE ( [Total Orders], PARALLELPERIOD ( DateTable[Date], -12, MONTH ) )
) 
Snapshot showing DAX calculated measure with variables
Orders YoY Growth (With Variables) = 
VAR _OrdersPriorYear =
    CALCULATE ( [Total Orders], PARALLELPERIOD ( DateTable[Date], -12, MONTH ) )
VAR _OrdersVariance =
    DIVIDE (([Total Orders] - _OrdersPriorYear), _OrdersPriorYear)
RETURN
    _OrdersVariance

Besides the fact that the measure without variables is inefficient because it requires Power BI to evaluate the same expression twice, it also would take more time to render in a visual as it takes more time to refresh.

The second measure expression is more efficient because it only evaluates the expression once because of variables in it. Also, it takes a shorter time to render in a visual as it usually takes a shorter time to refresh.

To demonstrate the effects of variables on refresh time, I have used Performance Analyzer in Power BI Desktop to show this as follows.

Snapshot showing query refresh on performance analyser for card visual on measure without variables

The diagram above shows that the measure without variables took about 170 milliseconds to refresh the card visual. The bulk of the time was used by the "Other" time required by the slicer visual for preparing queries, waiting for other visuals to complete, or performing other background processing.

Snapshot showing query refresh on performance analyser for card visual on measure with variables

The diagram above shows that the measure with variable took a shorter time of 127 milliseconds to refresh the same card visual. The time taken to render the visual and the time required by the slicer visual for preparing queries, waiting for other visuals to complete, or performing other background processing was also smaller than that of the measure expression without variables.

When troubleshooting performance issues, it can be helpful to further investigate the issues on external tools like DAX Studio, which can really provide more information on where to focus your efforts to resolve performance issues. I will create another tip on how to investigate performance issues using DAX Studio.

Next Steps


Last Updated: 2020-08-18


get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Data Analytics and BI professional mostly in Microsoft BI stack of tools. He is a BI Consultant with Avanade.

View all my tips





Comments For This Article





download





Recommended Reading

Power BI Performance Optimization Tips

Improve Power BI Performance with Horizontal Filtering

Power BI Histogram Example using DAX

Using Power BI with JSON Data Sources and Files

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI








get free sql tips
agree to terms


Learn more about SQL Server tools