Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
juju
Helper III
Helper III

Help with Hourly Data Model

 

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

 

 

Windows_10_x64.png

11 REPLIES 11
v-qiuyu-msft
Community Support
Community Support

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:

 

q1.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

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.

 

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.

Isn't that similar to the calendar table ?

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

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

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.

 

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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.