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.

 

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'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:

     

18 Replies

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

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

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        10 years ago

        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.