Forum Discussion

dbadmin's avatar
dbadmin
Icon for Advocate V rankAdvocate V
10 years ago
Solved

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

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.   Star...
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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's avatar
    Greg_Deckler
    10 years ago

    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:

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
dbadmin
Icon for Advocate V rankAdvocate V
10 years ago

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.