Select time from an hour filter filter & select entries which are open ( based on open& close time)

[hbaruas3]

Hi,

I have a table with a few entries each having start times and end times.

I would like the user to select from a range of times between 0-23 hours (1 hour intervals), and the result should show only the selected rows which are open during that hour (all inclusive). For e.g.

- if the user selects 2 pm from the dropdown, only Id#3 should show up

- if the user selects 6 pm from the dropdown, all 3 Ids should show up.

- if the user selects 4 am, then Id#2 and 3 should show up.

Id	Open Time	Close Time
1	5 pm	8 pm
2	4 am	7 pm
3	12 pm	5 am

I have been a regular user of Tableau, and have been using Power BI for a while.

The solution below would have been easy for me using parameters in Tableau, but not sure how to proceed in Power BI, hoping the community here can assist.

How do I create this filter with a dropdown option for every hour in the day 0-23, and get the result I am expecting above ?

I read posts about using Generate Series (DatesBetween) in the forum.. but does not seem to be the right solution for me.

Thanks for having a look.

[Anonymous]

You can create a disconnected table and use the Time field from that table in your slicer. You can then use selectedvalue function to harvest the value from slicer and use it in a measure acting as a flag, like:

isOpenFlag =

var selectedHour = selectedvalue('DimTime'[Hour])

var open = max('OpeningHours'[Open Time])

var close = max('OpeningHours'[Close Time])

return if ( selectedHour >= open && selectedHour <= close, 1, 0 )

You can then use this measure in your filter pane on the table visual as a filter.

If this solves your problem, please mark as solution.