Forum Discussion

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

Community Champion

10 years ago

dbadmin - Actually, I think I have the answer without having to key in a bunch of data. In your fact table, create a new column:

Hour = HOUR([Hours])

Then, do the same thing in your Hours table essentially.

Then, relate the two tables based upon your "Hour" columns. That should get things right in your model.

Edit: OK that will take care if you only wanted it in Hours now that I think about but the same basic concept should apply, just come up with the same process but do it by the half hour. There is a MINUTE function and you could use that to decide if it is greater than or less than 30 and create your key with that in mind.

Greg_Deckler

Community 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
Advocate 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
dbadmin
Advocate 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
  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:
  - AlexGorev
    Power BI Team
    10 years ago
    Please also make sure you voted for the idea below...
    
    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7865136-timeline-slicer-for-date-month-quarter-year
    
    Alex.
dbadmin
Advocate V
10 years ago
Got it to work! :) Thanks so much for your help! :)
- dbadmin
  Advocate V
  10 years ago
  Greg_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! :)
  - dbadmin
    Advocate V
    10 years ago
    Greg_Deckler Kind of like creating buckets I think...
Analyst_in_prog
Frequent Visitor
2 years ago
hey greg , i am having some problem in power bi , i have a time column , i want to extract start hour, if minutes are less than thirty else end of our ,
for example 12:40:00-->13:00
12:30---->12:00
how can i do it in power BI