Forum Discussion

dbadmin's avatar
dbadmin
Advocate 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
Community 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: