Hey everyone! I’m a very new Power BI user who”s in over his head at the moment, but I’m loving it! This is probably a dumb question, but as I’ve watched many of Sam’s videos, I’m getting up to speed on the concept of using a date table. However, I’ve never seen any mention of that table actually being marked as a date table in a Power BI using “Mark as Date Table.”
My question is… is that just a step that everyone does, but you take it for granted so it’s never mentioned? Or are there reasons that you don’t do it? Just trying to learn what the actual implications, pros, and cons are of marking or not marking the date table as such.
In reference to my question above, what I’m seeing in these videos is that the tendency is to create the date table and manually create the relationships needed to make it work. I do NOT see anyone going to the “Modelling” ribbon and clicking “Mark as Date Table.” Is that because clicking the button is just another way to create the relationships that are created manually on the videos?
No, marking as date table creates no relationships, nor vice versa. What marking as date table does is validates that the date field specified fully meets this list of criteria:
contains unique values
contains no null values
contains contiguous date values (from beginning to end)
if it is a Date/Time data type, it has the same timestamp across each value
Beyond specifying the proper relationship(s) between the date table and other table(s) in the model, the marking step is critical to maximize time intelligence capabilities. It will get rid of the automatic date hierarchies created by Power BI (which many people, me included, find cumbersome and difficult to work with), and certain time intelligence functions expect to find in their first parameter a date field that meets the above validation criteria.
Also, marking your data table as such allows you to use the built in time-intelligence correctly. What happens when you use, say, DATESYTD is that is basically a short-cut that the DAX developers produced. In the background there is actually a FILTER ( ALL (Date)) being used, but you just dont see it/have to use it when using a time intell function. But only works on standard calendars, which is why you need to explictily have the the FILTER ( ALL(… when using custom calendars.