Forum Discussion
Advocate VHow to create a time range filter in Power BI report? (Part 2)
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 - 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:
Advocate VGreg_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! :)
Advocate VGreg_Deckler Kind of like creating buckets I think...
- Greg_Deckler
Community Champion
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.