When creating our latest course "DAX for Power BI" ("DAX leren gebruiken"), we scratched our heads about the size of our Power BI files. Even small data models took already tens of MBs. Here's why:

If you are creating new Power BI reports, by default the "Auto Date/Time" function is turned on. This means that Power BI generates a date table for every single date field that it encounters in your model - as long as it's not involved in a relationship.

Power BI hides these automatic date tables for you, but exposes them via the column itself: if you click on a date column, automatically an entire date hierarchy will be displayed in your visual.

Works great, right? Well, only if you really need it.

If you already have one or more Date Dimensions that you use inside your datamodel, chances are that you don't need those hidden tables, because you already have your own Dimension Tables for that.

If this is the case, then I urge you to disable the "Auto date/time" option for your data models. They can grow your files quite a bit, especially when you have any date field with large values. In our case, the World Wide Importers dataset (we loaded only dimension stock item, dimension date and fact sales) had a "type 2 dimension", so every row in this dimension includes a start- and an enddate that tells you when the values of this row (for example, the prices) were actual. Within type 2 dimensions, it's common to choose an "end date" far in the future for rows that have no enddate yet - for example 31-12-9999.

Guess what? We ended up with a hidden Date Table containing dates ranging from 01-01-2012 to 31-12-9999. Approximately 2.9 million rows with lots of unique values. Is that a big table? Not for Power BI. But it will make a difference in your file size, especially for the smaller ones.

In our case, the "auto date/time" tables grew our model from 5 to 31 MB.

Of course - there are some functional reasons as well why you shouldn't just go with the automatic date/time. See Alberto Ferrari's article "Automatic Time Intelligence in Power BI" for example.

While we're at it, maybe it's time to take your Power BI skills to the next level - and really learn how to build data models using DAX?

Time to take the next step in your Power BI reports? Check out our new DAX course! (Or, if you're just getting started, why not attend our one-day really-hands-on workshop "Dashboards and Reports using Power BI"?)

DAX leren gebruiken