Forum Discussion
Date Table: If/When needed
Hi CahabaData,
Based on you description, you want to know whenever you need to use a date table, right?
If as I said, you can refer to below scenarios which you need to use date table:
1. many to many relationship.
If you have two tables both contain many duplicate date records and you want to create relationship between them, you should add a date table to link them.
2. Get specify date between start date and end date.
Sample: get the weekend from 2010 to 2015.
3. Use a slicer to filter multiple tables(contain date field).
>> the hidden automatic Date table and it automatically joins to all tables/fields that have a date field type...
You have a misunderstanding of the hidden table, the hide table is created by time intelligence feature, the hide table(date hierarchy) is analysis by date field, it only related to the column which used to create the table, not automatically joins to all tables/fields that have a date field type.
In addition, you could work through ‘Scenario 1’ without create a date table , the time intelligence feature is enable on the newest version of power bi desktop(2.38.4491.282).
Regards,
Xiaoxin Sheng
I tried your rainfall example by making the two tables. Each table had every date from July 1 2016 - September 13 2016. For rainfall, I used a random number between 0 and 5, and for umbrellas sold I used a random number between 0 and 50.
I didn't try relating them because I don't think it's a relationship that makes sense. I created the two simple measures to just sum up rainfall and umbrellas sold, then added the rainfall measure to the report canvas to create a chart, and added the Date field to the Axis. By default, it was aggregated by year, and looks nonsensical:
It took me a while to even figure out what the 2,000 and 2,500 were. They're years. Even though I only have 2016 data, it shows the axis with year 2,000 and 2,500. Hovering over the column shows Year 2016. Yuck.
Since we wanted to try by month, I exed out the Date hierarchy except for month. Looks better:
Then I added my Units Sold measure to the Values section of the chart and got this:
Well that's just wrong, and shows the total of the amount sold across all of the dates, and then extends that into months that weren't even on the chart. I understand the total, since there's no relationship between the two or a common calendar, but it doesn't "just work" without some intervention then.
And when I try dropping one of the Date fields on the canvas to make a slicer, my only option for the Date Hierarchy is by Year, so there's no way to filter in a slicer by month without at least a new column. You can however use the Date in the Visual level filter of the chart and filter out certain months.
I'm on the latest July release. Can't check right now if there were improvements for this in August. Maybe this would show the improvement if nothing else.
hmmm could have sworn I saw a tutorial on a similar example - need to hunt that down.... I thought it worked without an explicit date table but I see what you are saying... will need to spend some time on this...
KGrice What I found is that to make a single visual with both Umbrellas Sold & Rain Amount as values along a common time line axis - - that I had to join the Date fields of the 2 tables together.
In my mock up both tables had every date - so that a join is going to work in terms of providing all data. If both tables did not have all the dates then some of the data would not be included and that would be a problem.
In the tutorial video (URL given in my original post) the speaker displays a hidden automatic Date Table. I guess it's role is limited to providing the embedded Time Intelligence functions so that if one had a single table without all dates - that you do not need to create a Date table for Time Intelligence.
However in a multi table scenario one needs all dates. If the core tables do not provide those then a Date Table needs to be created....which is what XS's post stated.