Advanced Time Line Slicer - Sorting data by shifts - up to every half hour

[heathernicole]

Hello! I absolutely LOVE Power BI - have the priviledge of being able to work with it in several different companies. Our company is made up of several smaller companies managed by one larger umbrella company. I currently work a couple of different positions for three of them. 

I use Power BI in our R&D company and then for two other companies for more sales oriented reporting. While I'm still learning a lot and have a lot to learn about Power BI (namely DAX and M) - I truly enjoy what I do. 

So here's my first post:

This project is still very much in development - the creating pre-defined shifts as a filter is still being worked on. Thanks to the Power BI Community and @Greg_Deckler who's diligently been helping me work through this; significant progress has been made. @Sean and @AlexGorev were also in on this on the forum. Thanks to each of you! 🙂 

Here's where I'm at in my development: 

I am currently using the Advanced Time Slicer published by Cambridge Technology Partners. This is a neat visual with a display of the data at the top of the graph and a time selector at the bottom. 

Advanced Time Slicer

The goal is to sort the data by shift and or customized time ranges.  The first goal was to create a time_table (with 48 half hours) to equal a total 24 hours. This is created in a MySQL database - rather simple setup for now: 

time table connected to Power BI

Actual database time_table looks like this: 

time_table in Mysql

The challenge after creating this was setting up the relationships properly so that the data would filter properly. You can follow the discussion here: Community Forum Discussion - How to create a custom time range filter.

@Greg_Deckler suggested adding some custom columns within the time table and the fact table (the data needing to be filtered).

Custom Column formulas

Here's an example of what the updated tables looked like. NOTE: these custom columns were created in Power BI - not the database. I've found that the data modeling is almost a lot easier in Power BI than it is do in the databases in some cases. Just another reason Power BI is such a great tool! 🙂 

Fact Table - data to be filtered with Key (for relationship)Time table with Key (for relationship)

The ending result is a slicer that works with a slicer - but also is compatible with other visuals as well. The end goal - making the end-user feel like they have complete freedom / control in analyzing the data. 

Before adding time filtersAfter Adding time filtersAdded time filters and use of time selector in actual visual

The next phase of this is to create the Preset Shift filters that displays data for entire shifts (i.e. 6am- 4:30pm - 3pm - 3:am -whatever the desired shifts are). Also it would be nice if there was a little bit more practical way to select a range rather than having to select each part in that range. 

Time Slicer - Single Select turned off

The only thing missing that would make this Advanced Time Slicer a little better is if the tool tip displayed the data at each point rather than just a Total of ALL the data (or average, etc...). Otherwise GREAT visual. 

Tool Tip

Also - using the custom time slicer (with half hours) allows you to drill down to the minute using the Advanced Time Slicer section. 🙂 Pretty neat. This may not be useful for all data - but it's still good to know what it's capable of.

ANY ideas on how to do this better are WELCOMED! Would love the feedback!

Will post updates once I figure out how to create the preset shift filters. 🙂