Views with date column vs joining to a calendar table

[Anonymous]

Hello, I'm a SQL Developer new to Power BI.

I'm trying to understand this best practice by always adding/joining to a Calendar dimention table. Why?

Let's say I have a prebuild view - Sales. It is not start schema. It is one view with Dimentions and Facts together. Also I've been told this is wrong too.

It is being suggested that for filtering I should join to a Calendar table and filter/use slicer on that.

My question why can't I use the date from my View. No extra steps needed.

Best.

[Shaurya · Accepted solution]

Hi @Anonymous,

The date column in your table may or may not have all the dates. The reason why it is not suggested to use this column is because you will not be able use Time Intelligence functions which require a Date Table with continuous dates and no null/error/duplicate values. On that note, you should not be using the date column from some other table in slicers as well because that might affect visuals which are using time intelligence measures.

Did I answer your question? Mark this post as a solution if I did!

[Burningsuit · Accepted solution]

HI @Anonymous 

Without a Date Table, any dates in Power BI get a default "Date Hierarchy" of Year, Quarter, Month & Day. That's fine if the Year you want is the Calendar Year (January to December) and the Quarters you want are Qtr 1 = Jan, Feb, Mar.  But it's not so good if you want you Year to be a fiscal year (Say April to March) or if you want Qtr 1 to be Apr, May Jun. Or in fact anything other than the simple date hierarchy you get as default. Date Tables can work with Month Numbers rather than Names, Week numbers, and many other differing date formats. So if you want to do anything other than a simple date heirarchy you need a Date Table.

see: Power BI: How and why to create a date table | AccountingWEB

You also asked about a Star schema of facts and dimentions rather than one big flat table. There are many reasons to prefer a Star Schema, efficiency is one. Power BI is built to work with Star Schema and make most efficient use of resources, accessing one big table may be slow if you have a lot of data.

My own favourite reasons though is simple. Lets assume you have a flat table of Customers and Transactions versus a Star Schema of Transactions (Facts) and Customers (Dimension). In the flat table it is very difficult, possibly impossible, to identify Customers with no Transactions, because the very reason they are in the table is because they have a Transaction, even if it's zero value. In the star schema it's easy to identify Customers without Transactions.

see : Power BI – Star schema or single table - SQLBI

Hope this helps

Stuart

[Anonymous]

I get it. For me it is easier to do all joins in SQL of snowflake and have one view. Time to get used to new way of doing things.

Thank you.

[Shaurya · Accepted solution]

Hi @Anonymous,

The date column in your table may or may not have all the dates. The reason why it is not suggested to use this column is because you will not be able use Time Intelligence functions which require a Date Table with continuous dates and no null/error/duplicate values. On that note, you should not be using the date column from some other table in slicers as well because that might affect visuals which are using time intelligence measures.

Did I answer your question? Mark this post as a solution if I did!

[Anonymous]

So another question comes up, if i use the date from my one/main view the the slicer the options i will have are only those available in the data. So let's say my slicer is a range. If use the date from the view my options will be limited to the dates available. (It automatically knows min and max) If i use a calendar i don't have limitation. I have to figure it out.