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

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

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

     

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

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

         

         

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

      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.