How to USE a date table

[therealomacoder]

Scenario:

Model: 1 fact table using DirectQuery.  The table contains 14 different dates.

In PowerBI Service, I want the user to be able to use the month portion, date portion, month date formatted, quarter, etc from any of those 14 different dates for any of their visualizations.

I read that I can add and "flag" a table as THE date table, but you can only have one. Then, you have to define relationships off of each date back to the date table. Then, since it's all one table, you have to create separate measures for every possible combination of everything you want to measure and for each of the 14 dates, using the USERELATIONSHIP function. (https://community.powerbi.com/t5/Desktop/Using-Date-Tables/td-p/347010)

This is very unpractical.

I feel like there's a huge secret about the "flagged" date table that I cannot figure out.

What I really need is my DATE table to automatically interact with every field in the model that is defined as a DATE. I feel like this is the true purpose of flagging a date table.

[Nolock]

Hi @therealomacoder,

the count of dim date tables depends always on users' requierements. In very many cases it doesn't make sense to have more than one dim table for one fact table because it is complex for end users to understand it.

However, you can create one dim date table in PowerQuery Editor, then create a reference to this dim date table in DAX with a different name and create a relation in Power BI Model. If you do it in DAX, you'll have just one dim date table in your dataset but many in Power BI model.

And the result:

[therealomacoder]

Interesting, so basically creating an alias table in DAX.

Not sure why you say that it is complex for end users to understand multiple date tables. From my perspective, this is way easier for an end user to utilize rather than loading the model with measures for every date and every "by" dimension utilizing USERELATIONSHIP.

I was hoping that once you defined a date table in the model, then PowerBI would automatically create a 'hierarchy' for every date in your fact table, using the objects from your declared date table. Then, no matter which of the 14 dates the user wants to use in PowerBI Service to build a report, they have every dimension off of the date table to use.

My hopes of PowerBI being this smart has dwindled. Still my perspective of declaring a "Date" table seems useless.

[Nolock]

Hi @therealomacoder,

you can do 2 different things. Either you create your own date dim table / tables or you can create a date hierarchy on every date/datetime column in your model ==> PowerBI is smart at this point in the way you expect 😉

(But be careful how big your pbix file becomes when having many date dimensions.)

[therealomacoder]

I'm not understanding why this is so complex for me to comprehend!! Please stay with me here... 🙂

Trying to be more open minded.

I tried two things:

ONE:

1a. I imported a date table, and I defined that as "the one and only date table" that a model can have.

1b. This did nothing automatically. This added no hierarchies to all of my dates on the fact tables.

1c. I published the report to PowerBI Service. There were no automatic drill or filtering capabilities on Month/Quarter/Year (all attributes on the date table).

TWO:

2a. Manually defined a date table like you describe using DAX. I then flagged this as "the one and only date table" that a model can have.

2b. This did nothing automatically. This added no hierarchies to my dates from the fact tables.

2c. I published the report to PowerBI Service. There were no automatic drill or filtering capabilities on Month/Quarter/Year (all attributes on the date table).

Thus... I still persist declaring a table as "the one and only date table that a model can have" as practically useless? I am not understanding what features this enables by "declaring" that table as "THE DATE TABLE".

I hope you make sense of my confusion and can point me to a how-to video, etc for the purpose of declaring a date table. Thank you @Nolock 

[JWilkin]

Did you ever gain any insight on this front?  I am in exactly the same position you were when you wrote this post and am hoping you have some resources to enlighten me about date tables in powerBI.  Thanks for any help!