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

[baskaran]

Hi,

I try to import the Project Online Content Pack and received this error: We couldn't import data from Microsoft Project online.

[clausm73]

In essense what you are trying to accomplish is "bucketing" your time values - into buckets of equal duration. There are several solutions to bucket problems from other bloggers:

Main my preferred order of reading:

http://www.daxpatterns.com/static-segmentation/

http://www.daxpatterns.com/dynamic-segmentation/

[pat_energetics]

I have typically found that defining the shift patterns and creating a custom column is much easier in Excel if you have access to the source time dimension table. Using the new SWITCH function to define the time ranges for each shift and returning "shiftname1" or "shiftname2" or "shiftname3" , etc works well.

[ImkeF]

Very nice article 🙂

Re the pre-defined shifts: Wouldn't it be easiest just to add a column to your time_table called "ShiftName" and apply the name of the shift to every single row? This would work like a month- or year-column on a calendar-table (that has been created on a daily basis) - as it just groups certain rows to the desired intervalls.

[Anonymous]

@ImkeF, that's how I would approach this. Something like this screenshot. One can then connect the tables and then filter on the "Shift" column.

[ImkeF]

@Anonymous Yes, that would work.

But still I'd prefer not to create an additional table, but just to add a column to the existing time_table.

[Anonymous]

I see. The SWITCH() function might work, then. It's bascially a compact one-line lookup function.

https://msdn.microsoft.com/en-us/library/gg492166.aspx

[heathernicole]

thank you to all for the comments / suggestions! 🙂     @ImkeF @Anonymous @pat_energetics 

I DID think about adding the third column. What messes with the logic there (unless I've overlooked something - which is highly probable) is our second shift. 

Our second shift runs into the NEXT day - 

First 6:30am - 4:30pm

Second 4:30pm day 1 - 3am day 2.

Third is a weekend shift running 4:30am - 4:30pm.

The problem I run into is this - If I want to see second shift - it brings up the 12am -3am of the SAME day which is technically part of the previous day's second shift results. NOT the current day.

I have been able to set it up to where I can select 1st and 3rd shift in one shot but it's second shift going into the next day that causes the issue. And I haven't found an effective (and efficient) workaround for that yet. 

My question to anyone who wants it - is how do I write the code to where it will select 2nd shift - that really goes into two days - from a business report perspective is one day. How do I tell it to look at the NEXT day's 12am - 3am NOT the previous day? Every way way I've tried it so far - that's the same wall I come to. I'm looking into M to see if there is a way to do it using that language if DAX isn't capable of it. Still doing the research. 🙂 

THANK YOU SO MUCH for the comments and suggestions. 🙂 Love this! Keep it coming!

[Anonymous]

I havne't tried this, it's just conjecture. And I'll try to describe what I'm thinking in terms of a lookup table.

What if you had a lookup table of DateTime values in the first column and the corresponding shift in the second.

 For the first DateTime value, you could use some soft of reference to start with a variable date, either using TODAY ors omething else, then for each subsequent hour, add 1 hour to the first value.

This is obviously a half-baked idea but maybe it will lead somewhere.

Ti=,=TODAY(),0.25,=B1 + C1
,,,
DateTime, Shift,,
=D1, 1st,,
=A4+1/24, 1st,,
=A5+1/24, 1st,,
=A6+1/24, 1st,,
=A7+1/24, 1st,,
=A8+1/24, 1st,,
=A9+1/24, 1st,,
=A10+1/24, 1st,,
=A11+1/24, 1st,,
=A12+1/24,2nd,,
=A13+1/24,2nd,,
=A14+1/24,2nd,,
=A15+1/24,2nd,,
=A16+1/24,2nd,,
=A17+1/24,2nd,,
=A18+1/24,2nd,,
=A19+1/24,2nd,,
=A20+1/24,3rd,,
=A21+1/24,3rd,,
=A22+1/24,3rd,,
=A23+1/24,3rd,,
=A24+1/24,3rd,,
=A25+1/24,3rd,,
=A26+1/24,3rd,,

[ImkeF]

I'd probably add a column "Adjusted Date" that subtracts 1 day for all times before 3am.

Creating DateTime-Table like suggested by @Anonymous also seems possible, but this would eat some resources as far as I can see.

Otherwise you'd have to edit serious DAX to: Adjust the filter-context to exclude the start of the day from the current day and include the first 3 hours to the current date.

[onlinetimesheet]

Thank you very much for this post .... you saved my day.
online timesheet also helps to manage time