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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Element115
Power Participant
Power Participant

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
Power Participant
Power Participant

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

8 REPLIES 8
Element115
Power Participant
Power Participant

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. 

Dear @Element115 ,

 

I have the same requirement, and so far, I couldn't implement it using Power BI.

Could you please share your solution or the pbix file?

 

Thanks and best regard,

Farhad

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

Hi @Fowmy ,

Would you please upload the pbix file again? (I couldn't dowload it using the old link)

Thanks and best regards,

Farhad

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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