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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Element115
Post Prodigy
Post Prodigy

Range slicer by time of day AND NOT by date.

I have the following business requirement:

 

Problem statement: Let the user slice the data using not only by a date range slicer, but also a time range slicer.

 

Meaning:  User can select start date 12/01/2020 to end date 12/07/2020 (using US date format) AND start time 14:45 to end time 20:15, for example.

 

Discussion:  The date range is no problem as Power BI already provides a built-in slicer capable of working with dates.  The problem is the time slicer, ie a slicer capable of working with hours and minutes to define an intraday range, just like a regular slicer is capable of defining a date range.  The built-in slicer doesn't do that.

 

Question:  Is there any way of achieving this? If yes, how?  DAX formulas? A custom visual from a 3rd party (have looked but didn't find anything)? 

1 ACCEPTED SOLUTION
Element115
Post Prodigy
Post Prodigy

I am happy to annouce that I finally managed to solve this and must say: The Power BI Tabular platform makes the implementation of this kind of solution extremely tricky! You've got to consider 4 different levels of filters. How insane is that for an algo so simple it would have taken me 5 minutes or less to implement in C or Python. 

View solution in original post

6 REPLIES 6
Element115
Post Prodigy
Post Prodigy

I am happy to annouce that I finally managed to solve this and must say: The Power BI Tabular platform makes the implementation of this kind of solution extremely tricky! You've got to consider 4 different levels of filters. How insane is that for an algo so simple it would have taken me 5 minutes or less to implement in C or Python. 

Where is the solution?

What do you mean?  Was I supposed to post the solution?

Fowmy
Super User
Super User

@Element115 

I came up with a solution, please check the attached file. I duplicated the date table to provide an additional slicer for time. You will have to select the date range then Time Start and Time End.

 

You can download the file: HERE

Fowmy_0-1607543173408.png

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

 





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Any chance you can reshare the file

Thank you @Fowmy I am afraid I was not clear enough in my problem def--I realize now I implied what I am going to write, and I apologize for that. Your proposed solution unfortunately only creates a time range--the same time range--within each day.  

 

The requirement I have is to define a date-time range across multiple days. In other words, for ex.:

 

start date-time: 12/01/2020 13:15:00

end date-time: 12/04/2020 15:30:00

 

so that all time points between these 2 dates are included in a table, and not the time points from 13:15:00 to 15:30:00 only for each date, ie

 

NOT this (let's assume an interval of 15 mins for each time point):

 

12/01/2020 13:15:00

12/01/2020 13:30:00

12/01/2020 13:45:00

12/01/2020 14:00:00

12/01/2020 14:15:00

12/01/2020 14:30:00

12/01/2020 14:45:00

12/01/2020 15:00:00

12/01/2020 15:15:00

12/01/2020 15:30:00

 

12/02/2020 13:15:00

12/02/2020 13:30:00

12/02/2020 13:45:00

12/02/2020 14:00:00

12/02/2020 14:15:00

12/02/2020 14:30:00

12/02/2020 14:45:00

12/02/2020 15:00:00

12/02/2020 15:15:00

12/02/2020 15:30:00

...
all the way like this til 12/04/2020.

 

Instead, we should also get the time points after 15:30:00 from start date until end date for all dates in between as a continuous timeline (by 'until end date' I mean the last time point on the end date would be 15:30:00 in this example, but the day before we would have time points all the way to midnight and continuing through the morning of the last date in the range to end at 15:30:00), and not only the same start time-end time range repeated for each day in the date range.

 

Is this explanation clearer?

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.