Grouping and Improved Date Slicer

Date Slicer & Grouping
Date Slicer & Grouping

In the October update of PowerBI Desktop we were given a number of really useful features, ranging from a new Date Slicer, Grid lines, Grouping, Binning, Top N Filters, and R-powered custom visuals.  For the full release on the October 2016 software release you can read more here.

For those of you who have followed my site you already know that I absolutely love the ability to create R-visuals within PowerBI.  If you want to learn more you can read the R script tutorials here.

As I’ve been exploring this October release of Power BI Desktop two features have really stood out.  First, the ability to use the Date Slicer and second the new feature of Grouping for Bar Charts.  In my daily work flow I have struggled in the past with solutions which are now solved.

Welcome to my love & hate relationship with time bound data sets.  Inevitability at some point you will encounter a need to manipulate data over time.  Common business questions will come in the form of:  What was my percent change compared from this month compared to last month?  What is my sales performance for this year? Are we up or down compared to the same period last year?  While these questions are simple I have found that calculating measures and subsequently building visuals can get very complex in a hurry.  Enter the Date Slicer.  Let me be clear, the Date Slicer will not solve all your problems, it does present a very useful interface that will let report users quickly navigate through their time delineated data.

What is the Date Slicer?  I’m glad you asked. Behold….

Date Slicer
Date Slicer

Here are a couple of key items to point out.  On the left side of the visual you are given the ability to select the starting date, and ending date.

Start and End Dates
Start and End Dates

The bottom of the Date Slicer has an adjustable time bar that allows quick time adjustments.

Changed Timeline
Changed Timeline

On the right side of the visual you can toggle between different date selection modes, Between, Before, After, and List.

Time Parameter Selector
Time Parameter Selector

Ok, enough about the Date Slicer, how about the Groupings feature.  Lets say you start off with a bar chart that looks similar to the following:

Sample Bar Chart
Sample Bar Chart

Well, maybe you’re only interested in Items 9, 10, and 5.  Grouping now allows the selection of multiple bars and by right clicking you can Group, Include, or Exclude.

Right Click on Selected Data Bars
Right Click on Selected Data Bars

Clicking group creates a new column in the Fields area that groups the items together and adds them to the chart legend.

Grouped Items in Bar Chart
Grouped Items in Bar Chart

Let me tell you this is helpful, especially when your bar chart looks like this:

Ugly Bar Chart
Ugly Bar Chart

Be honest, you have published a report or two when there were just way to many bars.  The different between the largest bar and all the small bars barely tells you any information.  This is why grouping is helpful.  When you receive data and you need to focus your story to the important pieces then grouping is your friend.

Enough babbling, let’s get to the tutorial.

Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query.  Click Connect to open the Query Editor.  Click Advanced Editor on the View ribbon.  While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor.  This tutorial teaches you how to copy and paste M code into the Advanced Editor.

let
 Source = Excel.Workbook(Web.Contents("http://powerbitips.azurewebsites.net/wp-content/uploads/2016/11/Sales-Data-Two-Years.xlsx"), null, true),
 Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Sales_Table,{{"Date", type date}, {"Product", type text}, {"Sales", Int64.Type}})
in
 #"Changed Type"

Rename the Query to Sales Data.  Once you’ve completed the data load your data should look like the following.

Load Sales Data
Load Sales Data

On the Home ribbon click Close & Apply to complete the data load.

Close and Apply
Close and Apply

Great, we are ready to start adding visuals now.  Add a Slicer visual from the Visualizations window and populate the visual with the Date field.  By default, the slicer will auto recognize that the field being added is a date and will automatically show the Date Slicer.

Add Date Slicer
Add Date Slicer

Next, add a bar chart visualization.  The Date field will be the Axis, and the Sales will be the Value.

Add Bar Chart
Add Bar Chart

Again by default the Date filed will be entered as a Hierarchy field.  Click the Drill Down button until you see a monthly view of the date.  This should require two clicks, the first drills down to quarters, then second click drills down to month level.  After doing this your visual should look like the following:

Drill Down to Month
Drill Down to Month

Add a second bar chart with following fields selected.

Second Bar Chart
Second Bar Chart

Sort the Sales by Product bar chart in descending sale order by clicking the Ellipsis and when the drown down menu appears selecting Sort by Sales.

Descending Sort by Sales
Descending Sort by Sales

Now that we have built a couple visuals and a Date Slicer, take some time to explore how the Date Slicer interacts with the bar charts.  In the example below I modified the starting date to 6/1/205 and the ending date to be 9/30/2015.

Modify Date Slicer Properties
Modify Date Slicer Properties

To utilize the grouping feature we will group Items 1,2,3 and 4 together.  While holding the control button click Items 1,2,3 and 4 on the Sales by Product bar chart.

Ctrl Click Items
Ctrl Click Items

Right click Item 3 an menu will appear, select Group.  Notice once you do this a new Product (Group) field appears in the field menu and the Product (Group) is automatically added to the legend of the bar chart visual.

Grouping in Bar Chart
Grouping in Bar Chart

To edit the grouping you can click on the Field labeled Product (groups) and select Edit Groups.  Doing this reveals the grouping dialog box.

Grouping Dialog Box
Grouping Dialog Box

You can rename the created group by double clicking the name Item 1 & Item 2 & Item 3 & Item 4.  

Rename Grouping
Rename Grouping

Change the name of the grouping to be titled Special Items.  Click OK to close the Groups dialog box. Notice how the bar chart updates the legend values to renamed grouping.

Rename Group
Rename Group

Well, that wraps up this tutorial.  I hope you enjoyed it.  Below is a live demo of what we built today.

Make sure you share the content if you liked this tutorial.

10 Comments

  1. Unfortunately there does not seem to be a way to also add the times to this slicer.

    • You are correct, this only works for dates. If you needed times, you would have to parse out the times into a separate column, then add a slicer for the time column.

  2. This date slicer is helpful, but on load can we default the dates to last 4 weeks and later give the user control to select the range ?

  3. Also, can we restrict users to select other days in the date slicer calendar except for Monday ?

    • Not sure exactly what you mean here, but everything that you build into the measure is what you allow the user to select. Thus, if you don’t want the user to select something, it shouldn’t be included in the date slicers.

  4. Is there a way to get the total duration of time (i.e. # of hours or number of days) within a given window as selected by the slicer?

    I need to calculate machine availability which is calculated:
    (total time – downtime) / total time.

    Here’s the challenge (by means of example):

    I am looking at machine availability over a 10 day period. On day 5 of the 10 day period, I experience a machine failure that causes downtime of a full day. Once I fix the problem, the machine is back working until the end of my time window. Calculating the availability should be simple, i.e. Availability = (10-1)/10 = 90%. The problem is that once I filter the data using the slicer, all data remaining in my dataset (which is the downtime events only) is the time and duration of the actual downtime event. This means I cannot get the total time (10 days) from Power BI. Is there a system variable indicating the time-period or start and stop dates of a slicer?

    • I believe what you are running into is issues with the filter context. When you select something in the slicer you are pick a single day or range of days. The calculation you are looking to derive is outside of the time window (Filter Context) of the slicer you are using. Two things,
      1. The DAX will become more complex because you need to modify the filter context outside of what has been selected. Here is an article on filter context: https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/#targetText=The%20filter%20context%20is%20the,different%20subset%20of%20the%20data. What you will likely need to do is adjust the calculation to automatically extend from the time selected back 10 days to count the number of failures.
      2. Some of these types of calculation are difficult to render in DAX, thus it is often better to look at how you would transform the data in either M or SQL before pulling it into the data model. My initial thought is to attribute a value of hours that each machine is available. If it is online for 8 hours a day, for each day aggregate the number of online hours. Then when you do the DAX calculation you can use calculate and datesbetween. Check out this video on this topic: https://www.youtube.com/watch?v=v88mEK0Fhkg

1 Trackback / Pingback

  1. Announcements for SQL Server along with some tips for Power BI | Guy in a Cube

Comments are closed.