cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
khush19
Resolver I
Resolver I

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_DateDateMonthYearValueDate
202011088-Nov-20168/11/2020 16:00
202011088-Nov-2018/11/2020 1:00
202011088-Nov-2058/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?

CustomerStartTimeEndTimehourDate
A2021-09-08 06:00:00.000 2021-09-08 12:30:00.000 06 2021-09-08 06:00:00.000
A2021-09-08 06:00:00.000 2021-09-08 12:30:00.000 07 2021-09-08 07:00:00.000
A2021-09-08 06:00:00.000 2021-09-08 12:30:00.000 08 2021-09-08 08:00:00.000
A2021-09-08 06:00:00.000 2021-09-08 12:30:00.000 09 2021-09-08 09:00:00.000
A2021-09-08 06:00:00.000 2021-09-08 12:30:00.000 10 2021-09-08 10:00:00.000
A2021-09-08 06:00:00.000 2021-09-08 12:30:00.000 11  2021-09-08 11:00:00.000
A2021-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?

2 ACCEPTED SOLUTIONS
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1634090887754.png

The final output is shown below:

vyalanwumsft_1-1634090958703.pngvyalanwumsft_2-1634090966640.png

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.

View solution in original post

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


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

7 REPLIES 7
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1634090887754.png

The final output is shown below:

vyalanwumsft_1-1634090958703.pngvyalanwumsft_2-1634090966640.png

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
Resolver I
Resolver I

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.


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

khush19
Resolver I
Resolver I

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? 

 

 


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

@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 


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors