Power BI Desktop Update – December 2017

My Spidey Senses are tingling….

Oh yes, it is that time again……..this time brought to you by your friendly neighborhood Spiderman – after all, he gives his stamp of approval on this month’s Power BI Desktop Update……they are going to make your spidey senses tingle!!

So what have Power BI brought us, let’s take a closer look shall we?

Reporting

• Q&A for report creation
• Bookmark cross-highlighting
• More bookmark flexibility
• Field properties pane
• Create a scatter visual from x- & y-axis groupings
• High density sampling for maps based on latitude and longitude

Analytics

• Drill filters other visuals

As you can see, there are a number of superpower Power BI Desktop update this month, swinging us from building to building with a crazy amount of adrenaline – whooooooohoooooo!!!!

Q&A for report creation

What best way to start this off, but by unleashing the power of Q&A (aka Natural Query Language) for Power BI Desktop – oh yeah!!

Let’s see how it works…

1. To start off, click on the ‘Home’ ribbon
2. Under the ‘Insert’ Group, click on ‘Ask a Question’ and an empty chart silhouette will appear.
3. Start typing and ‘Ask a question about your data’ and the magic will start to happen.

A few things to note when asking questions:

• Once you start typing, Power BI will automatically assume what you are looking for (a sort of intelli-spidey sense feature). So when you type for example ‘Sales’ in will bring up a list of all columns / tables with the name sales – and you can choose which one is relevant for your visual.
• Ask the questions in a natural way, but by using the names of the columns you want to use.
• A good logic flow for the questions are as follows:
  • show (not always required) ……
  • by …….
  • and ……
  • as …….
    sort ……..
  • sort descending / ascending (for tables / matrices / charts)
  • in this year / month
  • in the last year / month

In fact when you create a chart the “Old fashioned” way by dragging and dropping columns, values and measures onto your fields pane, the automatically generated title for that chart tells you what you can use in the natural query language – just add the ‘show’, ‘sort’ and ‘date’ filters. This is a good way to get used to what Q&A is expecting from you.

Let’s try an example by typing the following question: ‘show LINE TOTAL EXCL by ITEM LOCATION and SALES PERSON as map’.

Here are the results:

I’ve always really enjoyed using Q&A, but as a developer I spend a lot more time in Power BI Desktop than in the cloud – so this for me is a very welcome feature. Give it a try, it’s as easy as…….well……pie!!

Power BI Bookmarking with cross-highlighting

While this was added already to the November Power BI desktop update, I believe there were some issues with it – here it is again, this time with cross-highlighting super powers!!

Last time we showed you how to use bookmarks, the selection pane and how to create buttons which can switch your reports between line and bar charts. If you missed that blog, go check it out Here – sssssshhhhh……….it’s filled with superhero secrets!!

As a follow up, let’s see how we add our cross-filtered context of our report as a bookmark shall we?

Here we have our previously created and famous ‘Monthly Trend’ toggle switch report where with the click of a button you can go from bar charts to line charts – it’s like magic really…..only cooler!! We’ll use this report to drill-down into the monthly sales for May 2014, until we can see what has contributed towards it’s high sales for that particular month, and then we’ll save it as a Bookmark to impress our bosses at the next sales meeting.

1. Click on the Drill-down report button in the upper-right corner of the Monthly Sales chart.
2. Click on the sales for the month of May to drill-down to ITEMCATEGORY.
3. Click on the sales for the ITEMCATEGORY ‘Accessories’ to drill-down to ITEMLOCATION.
4. Click on the sales for the ITEMLOCATION for ‘Port of San Francisco’ to drill-down to ITEMNAME.
5. Disable the ‘Drill mode’ in the top right-hand corner.
6. Click on our highest sales for ITEMNAME, in this case ‘Dry-erase White Board Markers’ to cross-filter the other charts.

Cooool, now for the final steps…..

7. With the Bookmarks pane enabled, click on ‘Add’.
8. Name your new cross-filtered bookmark with an appropriate name – we’ll call ours Monthly Sales by itemname May 2014′.

And there you have it, a new bookmark, with cross-highlighting to improve your sales story to your stakeholders.

More bookmark flexibility

Followed on the last awesome updates on bookmarks and the selection pane over the past few months, Power BI listened to the community’s feedback and realized that users needed more flexibility in what is bookmarked (These guys are awesome aren’t they?). Users required the ability to control the properties of their bookmarks and which visuals, slicers, data on a report should be affected by the selected bookmark. As a result, Power BI added two new sets of controls to let you do just that.

Let’s look at these controls shall we?

1. Here they have added ‘Data’, ‘Display’ and ‘Current Page’ selections which allows you to choose if the bookmark will apply the “data” properties (these include slicers, filters and all “visual” properties) such as spotlight, the visibility, and/or when a user changes pages back to the page that was visible when the bookmark was added. Users can use a blend of these settings to insure that the bookmark does exactly what you want it to do. A good example of this is our visual change toggle switch blog, where you would likely want to turn the data properties off so that filters aren’t reset as users change visual types.
2. The second part lets you pick what visuals the bookmark applies to. Note that by default, all bookmarks apply to the entire page, so make sure you change this if the default is not what you want. The cool thing now, is you can choose if you want the bookmark to apply to the whole page or only part of the page, this will be great for side by side comparisons (See below).

For this exercise, let’s create a side by side visual with a toggle switch for each side and control these two sides independently of each other without affecting the other side. If you remember with the previous update, when you created a bookmark it applied to the whole report canvas, and not a specific section of the report. If you can’t remember how to create the toggle reports – check out the section on “Changing report types with buttons (Images) in this blog.

To do this, let’s see what steps you would follow:

1. Before creating your bookmarks, select the visuals, images or slicers required (visual or hidden from the selection pane) for the specific bookmark you want to create. Notice from the selection pane, I have selected both hidden and visible buttons and bar/line charts.
2. With your objects selected, click ‘Add’ from the Bookmarks pane to add the bookmark to your bookmarks pane. We’ll rename this one to ‘Bar Chart (Right)’.
3. To create the ‘Line Chart (Right)’ follow the same process – but this time make sure that you make visible from the selection pane the line chart and line selected toggle switch, and hide the ‘Bar Chart (Right)’ visual and toggle. Note that with the creation of the both these bookmarks that you select bar chart / line chart visuals and toggles.
4. In order to link the line toggle image to go to the ‘Bar Chart (Right)’, follow the steps below:
   a. With the ‘Line Chart (Right)’ bookmark selected, select the toggle image switched to ‘LINE’
   b. From the ‘Format Image’ pane, switch ‘Link’ to On
   c. From the ‘Type’ box, choose ‘Bookmark’
   d. From the ‘Bookmark’ drop down, choose the bookmark you want to go to when this image is Ctrl clicked – in our case it will be the ‘Bar Chart (Right) bookmark. Repeat this process also for the bar toggle image.

   

5. Click on the ellipsis next to the bookmark ‘Bar Chart (Right)’ to open the properties menu.
6. Select the option at the bottom called ‘Selected Visuals’. Do this also for the ‘Line Chart (Right)’ bookmark.

Note: Repeat these steps for the left side also. With all these steps completed, using Ctrl + Click on each of the toggles will switch only the right or left side charts – how cool is that!?!?!?

Which reminds me, check out this awesome compilation of bookmark functionality put together by Art Tennick – people are coming up with some very useful examples, and I’m sure there is lots more to come.

Field properties pane

Sometimes column names come in ways not necessarily understandable by all users / developers – especially if it’s not your data set and / or multiple people are working on the same reports. Field properties allow users to rename or add meta-data to their columns, which will make an even completely unfamiliar person aware what a particular column’s values are all about. It can also explain how particular measures have been calculated.

In order to access / use these properties, follow these steps:

1. Right click on a column or click on the ellipsis to the right of the column name, to open the field menu.
2. Click on ‘Properties’ to open the ‘Field Properties’ pane.

The Field Properties pane will now pop open.

To update the properties follow these steps:

1. If required, change the name of the column or measure.
2. Type a description for the column / measure or how it is related to other data or how it is calculated (whatever you will find useful here to describe the column)
3. With the properties updated, hover over the column or measure you’ve just updated.
4. Notice the table, column / measure name and description appear once you hover over the field (schweeeeet!!)

Another awesome bit here is that the description will also be shown if connected to an external Analysis Services model.

Create a scatter visual from x- & y-axis groupings

Until now, the scatter visual would only work if you entered two axis values and a category value – cool but limiting. With this new update, you are able to put any numerical value on either axis – but this time, don’t summarize as the aggregation type. This will become useful when trying to categorize numerical data points. Remember that ‘Don’t summarize’ is only an option for numeric columns, and will not work for measures.

For this example we’ll be using our sales data, let’s see how this is done:

1. To start, I’m putting the ‘LINE COST’ value on the X Axis and the ‘LINE TOTAL EXCL’ on the Y Axis. By default these values are summed – let’s change that so we can see the groupings.
2. To turn off the sum aggregate, click on the down-arrow next to ‘LINE COST’
3. Select ‘Don’t summarize’from the pop-up menu. Do the same also for ‘LINE TOTAL EXCL’.
4. Ok, so this is what it looks like now….needs a little more work.
5. To finish this graph off, let’s add ‘Year’ to the Legend, and ‘QTYNET’ to the Size field. Adjust the formatting by changing yearly value shapes for each year and the final result will look like this:

Pretty cool, right? I think spider man will be happy with the use of color in this scatter chart :-)

High density sampling for maps based on latitude and longitude

Is your map not showing all points? Don’t worry, with this update Power BI has extended it’s high-density sampling algorithm (already added into the scatter and line visuals) also for it’s map visuals (Happy spidey dance).

This new algorithm will better keep the shape of your data points while also surfacing those pesky outliers. It does his by prioritizing points that aren’t hidden by neighboring points. There is also a noticeable improved in experience with using latitude and longitude that exceeded the data point limit (This I like!!).

You can of course test this algorithm by turning the High Density Sampling on or off – let’s use a crime stats data set for 2006 in Sacramento, US. The sample shows crime occurrences in the area. Create a map with the lat / long values, and crime stats for the 2006 by district.  This is how you can go about switching between the two settings:

1. With the map selected, click on the ‘Format Pane’.
2. Click on the ‘General’ tab to open the properties.
3. Switch ‘High Density Sampling’ to ‘On’.

While I must admit, this probably makes a big difference with the points on the map – will your eyes notice…..hmmmm, depending how many points you have on the map…..probably not. Still good that they keep improving on this.

Drill filters other visuals

Finally for the last update to this wonderful spiderman superpower package, Drill filters for other visuals!!

This month Power BI is adding one of the most popular drill features – that’s right, the ability for other visuals on your report to be filtered as you drill down in your visuals. It allows you to drill down in a given category and also have it filter all visuals on your page by that same category. This new option is on by default for all new reports, but you can turn it off (or turn it on for older reports), through the option in the Format tab. Check it out below:

And that’s it folks!!

Some fantastic new updates from Power BI for the month of December – just when you think it can’t get any better….

Will you take the responsibility with these superhero powers to make the world a better place? I know I will…..

To follow our update blog, visit our blog page.  To learn more, attend our training.

Until next time, keep safe and stay blessed!!