Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dbadmin
Helper IV
Helper IV

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 - DataWithout Time Filters - NO DataWithout Time Filters - NO DataAdvanced Time Slicer Attempt (not enough detail)Advanced 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 

2 ACCEPTED SOLUTIONS

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

shifts.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

18 REPLIES 18
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Sure!Data trying to filterData trying to filter

 

Time TableTime 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!!!

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 

 

Got it to work! 🙂 Thanks so much for your help! 🙂 

 

 

@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 ColumnTime 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! 🙂 

@Greg_Deckler Kind of like creating buckets I think... 

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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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 hoursPartial 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.

 

 

 

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 KeyData with KeyTime with KeyTime with Key

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

shifts.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

Sean
Community Champion
Community Champion

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

 

Timeline Slicer.png

 

My bad, this was marked completed when Time Slicer custom visual was introduced. Link below points to the idea to have better native experience...

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13397166-better-date-range-filter...

 

Thanks,

Alex.

 

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 KeyData with KeyTime with KeyTime with Key

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors