Power Bi aggregating data till hour level

[khush19]

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?

[v-yalanwu-msft · Accepted solution]

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.

[v-yalanwu-msft · Accepted solution]

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]

Thanks alot,i was not sure how to do it,u r genius thanku 🙂

[khush19]

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

[AllisonKennedy · Accepted solution]

@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.

[khush19]

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?

[AllisonKennedy]

@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? 

[AllisonKennedy]

@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