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:
Sample File attached ......Sample File
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:
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?
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
In the query editor, make a reference table that points at your hour stamp column.
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.
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
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.
Add another table called Hours with following columns and it will have 24 rows as 24 hours of the day
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..
In your data table, extract hour from your date time field, call it HourKey and make it integer
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.
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:
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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!