Forum Discussion
Power Bi aggregating data till hour level
In power BI report i have requirement where we have date filter and hour filter, and based on selected hour and date ,fact data should be filtered.
SO i add a date dimension where i also add column to get 24 hours as values(01,02,03) which is used in slicer.
| PK_Date | DateMonthYear | Value | Date |
| 20201108 | 8-Nov-20 | 16 | 8/11/2020 16:00 |
| 20201108 | 8-Nov-20 | 1 | 8/11/2020 1:00 |
| 20201108 | 8-Nov-20 | 5 | 8/11/2020 5:00 |
and i have fact data which tell which customer was using which website till how long
so customer A using website B StartTime 2021-09-08 06:00:00.000 and endTime 2021-09-08 12:30:00.000 ,so when user select 2021/09/08 and hour 05 so no record should be shown and if user select 2021/09/08 "07" so 1 record should be shown.
Now to get this result set ,is it only way to divide my fact data and store in Powerbi till hour level?
| Customer | StartTime | EndTime | hour | Date |
| A | 2021-09-08 06:00:00.000 | 2021-09-08 12:30:00.000 | 06 | 2021-09-08 06:00:00.000 |
| A | 2021-09-08 06:00:00.000 | 2021-09-08 12:30:00.000 | 07 | 2021-09-08 07:00:00.000 |
| A | 2021-09-08 06:00:00.000 | 2021-09-08 12:30:00.000 | 08 | 2021-09-08 08:00:00.000 |
| A | 2021-09-08 06:00:00.000 | 2021-09-08 12:30:00.000 | 09 | 2021-09-08 09:00:00.000 |
| A | 2021-09-08 06:00:00.000 | 2021-09-08 12:30:00.000 | 10 | 2021-09-08 10:00:00.000 |
| A | 2021-09-08 06:00:00.000 | 2021-09-08 12:30:00.000 | 11 | 2021-09-08 11:00:00.000 |
| A | 2021-09-08 06:00:00.000 | 2021-09-08 12:30:00.000 | 12 | 2021-09-08 12:00:00.000 |
As my data is going to be huge,is there any way to acheive this without grouping fact till hour level?
Hi, khush19 ;
You don't need to break my fact data for each row, you could create a hourslicer table as slicer. then create a flag measure .
1.create a table.
hourslicer = GENERATESERIES(1,24,1)2.create a flag measure.
flag = IF(ISFILTERED(hourslicer[Value]), IF ( HOUR ( MAX ( [StartTime] ) ) = SELECTEDVALUE ( 'hourslicer'[Value] ) && MINUTE ( MAX ( [StartTime] ) ) = 0, 1, IF ( HOUR ( MAX ( [StartTime] ) ) < SELECTEDVALUE ( 'hourslicer'[Value] ) && HOUR ( MAX ( [EndTime] ) ) >= SELECTEDVALUE ( 'hourslicer'[Value] ), 1, 0)),1 )3.then apply it into visual filter.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.khush19 Are you wanting a between filter? If so then you don't need any relationship between the Date, Hour and Fact tables. You can do all the filtering with DAX.
Similar to my post about it here: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html
You'll need to combine the Date/Time selections from both slicers and compare to the Start Date/Time and End Date/Time columns in your Fact table.
If you're still struggling after the blog, let me know and I may have time to provide more specific example.
7 Replies
Hi, khush19 ;
You don't need to break my fact data for each row, you could create a hourslicer table as slicer. then create a flag measure .
1.create a table.
hourslicer = GENERATESERIES(1,24,1)2.create a flag measure.
flag = IF(ISFILTERED(hourslicer[Value]), IF ( HOUR ( MAX ( [StartTime] ) ) = SELECTEDVALUE ( 'hourslicer'[Value] ) && MINUTE ( MAX ( [StartTime] ) ) = 0, 1, IF ( HOUR ( MAX ( [StartTime] ) ) < SELECTEDVALUE ( 'hourslicer'[Value] ) && HOUR ( MAX ( [EndTime] ) ) >= SELECTEDVALUE ( 'hourslicer'[Value] ), 1, 0)),1 )3.then apply it into visual filter.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks alot,i was not sure how to do it,u r genius thanku 🙂
khush19 A true DimDate table does NOT have hour - you should have only 1 row per day in your Date table. I would suggest in your Fact table that you split the DateTime into Date and Hour columns, then connect Date to DimDate table and create a DimHour table to connect to Hour.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Hi Allison,
Thanks for the reply,I will change the table purely to have one dimdate and other table with 24 rows which means 24 hours.but still my question is do i need to break my fact data for each row?like StartTime 2021-09-08 06:00:00.000 and endTime 2021-09-08 12:30:00.000 so do i need to break one row into 06,07,08,09,10,11,12 rows and then push to powerbi?
khush19 In your fact table you just need the Start Date and Start Hour. You can do this in Power Query easily. Click Transform Data in Power BI, then in the Add Column tab click Date > Date only (with the start date column selected).
Select the Start Date column again. Click Add Column > Time > Hour.
You can then use those two new columns to relate to your date and Hour dim tables. Does that make sense?
How about end date ?as suppose user select 08/09/2021 and hour 07 then that record should come as start date is 08/09/2021 06:00:00 and enddate is 08/09/2021 12:30 so i need mapping from both start and end
khush19 Are you wanting a between filter? If so then you don't need any relationship between the Date, Hour and Fact tables. You can do all the filtering with DAX.
Similar to my post about it here: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html
You'll need to combine the Date/Time selections from both slicers and compare to the Start Date/Time and End Date/Time columns in your Fact table.
If you're still struggling after the blog, let me know and I may have time to provide more specific example.
