Have you seen articles or video headings like “I turned off this setting and reduced the size of my model by 60%”? Is it true? Yes. Not always, but it’s possible. Mentioned setting is Auto date/time and can be both useful and dangerous at the same time. For sure we can’t say that it’s always bad, but it’s bad when there is lack of knowledge about what is it and how it works. This is exactly what we are going to cover in this article.
What is Auto date/time setting?
Let’s start with where you can find it. When you enter Options and Settings in Power BI Desktop, you will find two sections: Global and Current File. Global settings as you may guess are related to all your Power BI Desktop files, while Current File settings impact only the file you have currently open. This part at least if very intuitive. In both sections we can find a group of settings called Data Load. This is the exact place where you can find the Auto date/time checkbox:
What happens when this setting is enabled? Assuming you have in your Semantic Model data in Import Mode, during data refresh Power BI will find all columns of Date or DateTime type and will create a Calendar Table for each of them. If you are using in your models Calendar Tables, if they are not imported from Data Source, you must probably create them with Power Query or DAX, using for example CALENDAR function. The same function is used by Auto date/time feature to create basic Calendar structure like presented below:
These Calendar Tables are hidden in the model, even from you! I will show you in next paragraph how to call such table and check the content.
Before we continue, let’s summarize when automatic calendar tables are created:
- Auto date/time setting is enabled
- You work with the data in import mode
- Column is of Date or DateTime type
- Column is not part of relationship on “many” side
- Column is not marked as Date column in Date Table
Why are Calendar Tables needed?
On top of the date column they provide full date hierarchy, to enable data filtering by Year, Quarter, Month, etc. However, they also have one important property. Calendar Table when it’s created, it doesn’t reflect the exact set of dates that you can find in a source column. Instead, DAX CALENDAR function creates full set of dates for all year available in the column, regardless of the exact values of minimum and maximum dates. For example, if MIN value of dates in column is in June 2023 and MAX date is in September 2024, CALENDAR function will create set of dates between 1st of January 2023 till 31st of December 2024. This continuity is important for DAX Time Intelligence functions to work properly. Bottom line is, Power BI creates automatic Calendar Tables for all the Dates Columns, to make Time Analysis easier for end users, and abstract them from the Data Modelling concept. Those hidden Calendar Tables are linked with your data using physical relationships, which are also automatically created and hidden from you.
Power BI is a tool that welcomes with open arms both Data Professionals and so-called Citizen Developers. The Auto date/time feature in Power BI is tailored for less advanced users, often business professionals whose primary focus lies beyond data modeling—such as managing supplier relationships or other operational tasks.
Seeing is believing
Auto date/time feature is enabled, but I mentioned that created tables are hidden from you. How can you check that the feature kicked-in? There are multiple methods. First one is directly in Power BI Desktop:
Columns marked with red color have Calendar Icon next to the name, and we can see available hierarchy that allows to select also Month, Quarter and Year. On top of that, we can see the difference while using DAX functions, because we can select any value from that hierarchy when calling such column:
It’s also important to mention here, why only two columns have the Calendar Tables created. Let’s go back to the requirements I mentioned in previous section. Calendar Tables are not created when column is on the “many” side of the relationship. In my model I created relationship with columns marked with blue color (one active and one inactive). That is why we see Calendar Tables created only for columns in red. Another thing I should mention here, is that column DateKey in my Calendar Table has the internal Calendar Structure created due to mistake I did on purpose. As I explained multiple times, Calendar Tables are not created for columns that appear on “many” side of the relationship, and DateKey appears on “one” side, that’s why we see the hierarchy created. But, could this be avoided? Yes. Again, let’s recall the bullet points on top explaining when automatic Calendar Tables are being created. If I did a better job while creating my Calendar, and I marked it as a Date Table, I wouldn’t have this problem. Once this is done, additional hierarchy is gone:
Another way to check if Auto date/time feature is being used is with DAX Studio. When we open this tool, we can see something interesting:
Blue color marks a template for Data Table structure. This template is used to create our automatic Calendar Tables, which we can also see here marked with red color. And again, we can see only two of them, I just explained above.
When we open VertiPaq Analyzer in DAX Studio we can also see the relationships summary. Columns in blue are linked with my Calendar Table -> they are on the “many” side of the relationship, that is why they were not used to create automatic Calendar Tables. Columns in red also have relationships but this time with the automatic Calendar Tables.
Thanks to this view, we recall what was said before. Auto date/time feature not only creates automatic Calendar Tables, but also relationships between them and our data.
How bad is it?
Maybe first question is, is it bad in a first place? Not always, but it can be. While it helps non experienced developers start analyzing the date, it can also get out of control for larger models. If feature creates multi-column structure for each Date/DateTime column in our model, we can assume that the price is larger model size. But how much bigger it can get?
Screenshot above shows you one of examples I dealt with, when disabling Auto date/time feature reduced the size of *.pbix file by 27%. That’s huge difference, especially considering that it was just few clicks away.
Larger model means more time needed to refresh it, higher probability of Out of Memory errors while consuming the report published online and may even cause the Semantic Model size to grow above limits for your Premium/Fabric Capacities. In the last scenario you will not be even allowed to refresh your model anymore.
Keeping the size of Semantic Model as small as possible is one of the foundations of responsible development, that’s why it’s very important to know how Auto date/time feature works, and that it can easily get out of control, by creating a problem on a single Semantic Model level, that scales for the entire enterprise.
Is Auto date/time feature to blame?
I didn’t see yet anyone asking this question, and, in my opinion, it is quite interesting one. If you paid attention to all the rules listed in the article, when automatic Calendar Tables are created, you could say that if everything is done the right way, meaning all the Date/DateTime columns are linked with main Calendar Table using “one-to-many” relationships, and my main Calendar Table is marked as Date Table, additional hierarchies are not created for any Date/DateTime column. If that was your thought – good job – you hit the jackpot.
Conclusion
We went through a lot of material, but what is the conclusion, is this feature really dangerous or not? Dangerous is a good term here, because we can’t say for sure that this feature is all bad. What we can say is that this is a double-edged sword. On one side, it was designed to help less experienced developers. On the other side, when the same developers are forced to create large models, they can easily create a situation where your company resources are not properly utilized, forcing you to spend more money on infrastructure.
On the bright side, experienced Power BI Developers know this feature well and know how to handle it. They can also spread good word in their companies, teaching others how to use this feature carefully. Organize trainings for your Power BI Community and make sure as many people are aware of it as possible.
For today that’s all from my side, I hope you enjoyed this article. As always, thank you for staying till the end and see you in next one 🙂
