How to create a time range filter in Power BI report? (Part 2)

[dbadmin]

This is my second go at this Time Range Slicer (previous post) thing- I created a date table, shift table and and hour / half hour table using the suggestions posted from my earlier attempt.

Starting with the hours (because I need to create a time filter), I created the relationships necessary - however when I try to filter by time, it doesn't work properly. I've also tried the Advanced Time Slicer visual and it works well, but I'm not sure it's developed enough for what we need it to do. Here are some screen shots...

Trying to use time filters: 

Without Time Filters - DataWithout Time Filters - NO DataAdvanced Time Slicer Attempt (not enough detail)

I need to be able to filter the data by time (up to the half hour). I'm willing to revamp my approach because I HAVE to have this in less than a few weeks. It seems so simple, but I can get it to work the way I need it to. ANY help will be greatly appreciated!!!!

@itchyeyeballs @greggyb @MiguelMartinez 

[Greg_Deckler · Accepted solution]

OK, here should be a full solution for fact table:

Columns:

Hour = HOUR([Hours])

Minute = IF(MINUTE([Hours])<30,0,30)

Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])

Recreate that same key in your Hours table and relate them to one another.

[Greg_Deckler]

@dbadmin - Can you post some sample data? Especially a sample of the fact table? Posting the other tables (not the date table) would be helpful as well so that we can easily recreate the model and figure out what is up.

[dbadmin]

Sure!Data trying to filter

Time Table

Technically... I don't need a shift table - they want to be able to select whatever time values they want to - but inevitably if I DON'T include that option, they'll want it. But my first goal is to get this time table to work before I work in the shift table. Besides that, I haven't quite worked out all the kinks yet (2nd shift hours cross between two days). 

THANKS!!!

[Greg_Deckler]

OK, let me see if I can take this and create some sample data on my end. It will take me a little bit because I will have to key in all the data.

[Greg_Deckler]

@dbadmin - Actually, I think I have the answer without having to key in a bunch of data. In your fact table, create a new column:

Hour = HOUR([Hours])

Then, do the same thing in your Hours table essentially.

Then, relate the two tables based upon your "Hour" columns. That should get things right in your model.

Edit: OK that will take care if you only wanted it in Hours now that I think about but the same basic concept should apply, just come up with the same process but do it by the half hour. There is a MINUTE function and you could use that to decide if it is greater than or less than 30 and create your key with that in mind.

[Greg_Deckler · Accepted solution]

OK, here should be a full solution for fact table:

Columns:

Hour = HOUR([Hours])

Minute = IF(MINUTE([Hours])<30,0,30)

Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])

Recreate that same key in your Hours table and relate them to one another.

[dbadmin]

Got it to work! 🙂 Thanks so much for your help! 🙂 

[dbadmin]

@Greg_Deckler Hello again! 🙂 

To try and develop this further. I've already implemented this in several reports and it works great. But I want to take it a little bit further. 

We have the time_table: 

Time table with Key Column

For end-user ease - I would like to create a filter based off of this table but that selects a range of times all at once. For instance:

6:00 am - 4:30 pm = Shift 1

4:30 pm - 3:00 am (the next day) = Shift 2

4:30 am - 4:30 pm (weekend shift) = Shift 3

Getting better at DAX but not great at it: isn't there a way to create a measure or calculated column derived from the main time table that would be able to select ranges? Then I could drop the Shift type column (Shift 1, Shift 2, Shift 3) into a slicer and filter by that. 

The reason for this: right now in order to select an entire shift I have to select 6:00 - 4:30  - each invidivual hour - half hour. That's not really efficient for reporting purposes. I've already tried a Shift ID table with Shift start and end times - this didn't work very well - or my lack of experience doesn't know HOW to make it work well. Probably the latter. 

Any thoughts? 

Thanks so much! 🙂 

[dbadmin]

@Greg_Deckler Kind of like creating buckets I think... 

[Greg_Deckler]

I can think of two ways of doing that. One way would be to just add a column "Shift" into your table and put in "Shift 1", "Shift 2", "Shift 3" into the appropriate rows. If you drop that into a slicer, it will then filter out only the hours for Shift 1, 2, etc.

The second way would be to create another dimension table, duplicate your key column, add the "Shift" column with "Shift 1", "Shift 2", etc. and then relate the tables based upon your key column.

Does that make sense? How is your "Hour" table created? The one with just all of the half-hour increments in it? Was that done with an "Enter Data" query or something else?

[dbadmin]

@Greg_Deckler I had thought of the shift 1, shift 2, shift 3 within the table. Which would work great except for third shift. Third shift is what messes it up. 

Our third shift here is actually Friday, Saturday and Sunday and it's 4:30am - 4:30pm. So it would throw it off because first is from 6:00am - 4:30pm and second is 4:30pm - 3:00am (the next day).

My hour table was created as a table in a MySQL database - it looks like this:

.Partial image of time table - is a full 24 hours

I've thought about setting it up to where it would look like this:

ID      Shift 1         Shift 2        Shift 3

1        6:00am         4:30pm       4:30am

2        6:30am         5:00pm       5:00am

3        7:00am         5:30pm        5:30am

4        7:30am         6:00pm        6:00am

5        8:00am         6:30pm        6:30am

So on and so forth....

Basically make each shift it's own column... but I don't know what that would accomplish... I've just started fooling around with that idea.

[dbadmin]

OK,  I took a stab at it - but now it's not filtering at all. The relationship was created without any errors - not sure what's causing the hiccup now.

Data with KeyTime with Key

[Greg_Deckler · Accepted solution]

@dbadmin - OK, I finally had time to sit down with this and model it out. Here is what I did:

I have two Enter Data queries like this:

Shift

2:30:00 AM

3:00:00 AM

3:30:00 AM

4:00:00 AM

2:00:00 PM

2:30:00 PM

3:00:00 PM

3:30:00 PM

Hour

2:22:03 PM

3:33:33 AM

2:23:23 PM

3:33:33 PM

Shift table custom columns:

Hour = HOUR([Shift])

Minute = IF(MINUTE([Shift])<30,0,30)

Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])

Hours table custom columns:

Hour = HOUR([Hours])

Minute = IF(MINUTE([Hours])<30,0,30)

Key = CONCATENATE(CONCATENATE([Hour],":"),[Minute])

I could then create a measure like this in Shifts:

CountofHours = CALCULATE(COUNT(Hours[Hour]),RELATEDTABLE(Hours))

I could then create a visualization like:

[dbadmin]

@Greg_Deckler - Hey 🙂 Thanks so much for trying to help me with this! I'm hopping back and forth right now between positions. It's taking me a little longer to respond than normal. 

This isn't quite what I was going for - I don't think. 

I'm trying to create a filter or a slicer that I can use to filter data. Kind of like with dates - but I can't get it to work properly. 

I've got the Timetable created and I could create some other visuals I'm sure. But I need it as a filter on the report. 

Is it possible to do this? I can't seem to find anything similar to it related to Power BI.

[AlexGorev]

Please also make sure you voted for the idea below...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7865136-timeline-slicer-for-date-...

Alex.

[Sean]

@AlexGorev why does it say completed? Can't Vote?

[AlexGorev]

My bad, this was marked completed when Time Slicer custom visual was introduced. Link below points to the idea to have better native experience...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13397166-better-date-range-filter...

Thanks,

Alex.

[dbadmin]

OK,  I took a stab at it - but now it's not filtering at all. The relationship was created without any errors - not sure what's causing the hiccup now.

Data with KeyTime with Key