Help with Hourly Data Model

[juju]

I have tried over several days to figure this out without success.  Throwing it out here with my sample data file.

I have a table with hourly data. However, I have also created an aggregated summary table on a day level ( for easier connection to my Calendar table) . Then I have a calendar table.  Most of my analysis / reports is done on the summary table. However on occasion, I need to analyze the hourly data. I am finding it difficult to relate the two tables - the original data table and the summary.  My Issues:

1. I cant create an index on the summary calculate table to connect the two tables
2. I cant use slicers built with data from the summary table to drill into hourly data from the data table 

Sample File attached ......Sample File

[v-qiuyu-msft]

Hi @juju,

I'm talking about the issue you write in the same files. You need to place the Channel column from the DataTable in the slicer to filter the data. See:

Best Regards,
Qiuyun Yu

[dkay84_PowerBI]

Does your hourly data have a date stamp as well as time?  If so, you can extract just the date and use that field for your relationship.

With that said, I'm not sure I understand what you are trying to do?  You want to be able to see hourly granularity when you filter by date?

[juju]

I tried your suggestion about extracting just the date to use as a relationship field - didn't work . The date field is not unique in both data and summary table. 

I am am looking to have access to hourly granularity for charts or for example average value by hour aggregated across several days 

[dkay84_PowerBI]

In the query editor, make a reference table that points at your hour stamp column.

Extract date.

Remove the time column.

Remove dups of date column.

Use this table to connect both your date field of your data table and the aggregated table.

In other words, this referenced table will sit between the two tables you wish to create a relationship between.

[juju]

Isn't that similar to the calendar table ?

[dkay84_PowerBI]

Your problem is that your relationships dont have crossfiltering set to both directions.

[juju]

Do you mind illustrating with my sample file? I am not quite following...

[dkay84_PowerBI]

I too am not 100% sure of what your desired end result is, but if you go to the relationship view and double click on the relationship line, you can try setting the crossfilter direction to "both" and see if that solves your problem.

I'm happy to look at your sample file

[juju]

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.

[parry2k]

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.

[juju]

Hi @parry2k

Thanks for chiming in. Yes, my summary table is precalculated - which is making things difficult for me.  I have a summary table because I haven't figured out how to aggregate the following metrics with DAX formulas over the data table with hourly granularity.  I thought summarizing by day in a calculated table makes things easier:

1. Total value by day ( 24 hour aggregation )
2. Average value by day ( average of aggregated hourly data, over a number of days )
3. Max daily value ( Max of the computed daily values over a number of days )
4. Max hourly value - Max od hourly data over a number of days

Then of course, there are the higher level roll ups - MTD, QTD, YED etc.  I am yet to find any reference to dealing with hourly data rollups or aggregation in Dax.