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….
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.
The bottom of the Date Slicer has an adjustable time bar that allows quick time adjustments.
On the right side of the visual you can toggle between different date selection modes, Between, Before, After, and List.
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:
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.
Clicking group creates a new column in the Fields area that groups the items together and adds them to the chart legend.
Let me tell you this is helpful, especially when your bar chart looks like this:
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.
On the Home ribbon click Close & Apply to complete the data load.
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.
Next, add a bar chart visualization. The Date field will be the Axis, and the Sales will be the Value.
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:
Add a second bar chart with following fields selected.
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.
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.
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.
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.
To edit the grouping you can click on the Field labeled Product (groups) and select Edit Groups. Doing this reveals the grouping dialog box.
You can rename the created group by double clicking the name Item 1 & Item 2 & Item 3 & Item 4.
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.
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.
Very helpful tutorial! Thanks for posting it.
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.
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 ?
You can now in the service. To do this, you will have to save the file with the Last 4 Weeks slicer selected. Then upload to PowerBI.com. This will save the filter state for when the report opens.
It is important to note that released this month the PowerBI.com service allows users to save the last filter state (Persistent Filters). This might confuse some users about the default settings of filters. Read the blog post here: https://powerbi.microsoft.com/en-us/blog/announcing-persistent-filters-in-the-service/
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.
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
Love the content from Guy In A Cube. Make sure you check out his Youtube channel!!