Forum Discussion
Help with Hourly Data Model
I will try to explain my end result :
My summary table doesn't have hourly data. All my reports are based on the summary table, including slicers. However, whenever I have a need to report on hourly data on the same report, I cant. There is no direct connection from the summary table to the original. I have tried all your suggestions above - including creating a reference date table which sits between the two - Got an error message - hence my suggestion to table a look at my sample file to undersand my problem. I will appreciate any help I can get. Been at this for days.
Here is what I think you need.
Step 1
Add another table called Hours with following columns and it will have 24 rows as 24 hours of the day
HourKey, StandardTime
HourKey will be integer unique 0 to 23
STandard Time will be something what you want to show like "1:00:00AM" and so on..
Step 2.
In your data table, extract hour from your date time field, call it HourKey and make it integer
Step 3:
Set relation between your hour table and data table on hour key.
Add slicer from your hour table and this will filter the data by hour.
Now only thing which I'm not clear is if you have Summary Table precalcuated, as this doesn't have link to hour table, you will not able to filter summary table but I'm not sure what is the role of summary table if you already have data table. If you can provide more information, will able to help.