Power BI Update – November 2017

Hey everyone!! It’s that time again…..now some of you might wonder, is it hammer time? Well it’s always hammer time of course ;-) but no, it’s

Power BI Update time!

With this November 2017 Power BI update comes a number of very welcome features and additions.

Let’s have a look at what they have for us this month shall we?

Reporting

• Control z-order through the selection pane
• Lock objects on your report
• Rule-based conditional formatting for table and matrix
• Cell alignment for table and matrix
• Report options for slow data sets
• Esri Plus Subscription for ArcGIS Maps for Power BI
• Filtering performance improvements

Data connectivity

• Impala connector – support for Windows Authentication

Query editing

• Add Column From Examples improvements

Analytics

• Cell-level formatting for multidimensional AS models for multi-row card

So let’s dig deeper and see how each of these are used in the new Power BI Desktop application.

Reporting

Control z-order through the Selection Pane

Last month Power BI also released the awesome selection pane, which allow you to show / hide objects on your report with the click of a “eye” button – ouch!! Imagine being poked in the eye with a mouse pointer all day long ;-) No thanks!!!

Power BI has extended the selection pane to let you easily change how objects overlap and show up on your canvas. The selection pane is now sorted so the objects higher in the selection pane list show in front of objects that are lower in the selection pane list.

Let’s look at the z-order through the selection pane:

1. Notice the two toggle button images now by default grouped together, one hidden, and one visible.
2. Notice the two ‘Quantity Vs. Prior/Target’ reports grouped together, one hidden, one visible.
3. Notice the two ‘Monthly sales Vs Prior/Target’ reports grouped together, one hidden, one visible.
4. Select any object from the selection pane and use the up or down arrows to put it either behind or in front of other objects.
5. Use the ‘Show all’ or ‘Hide all’ buttons to do just that, show all or hide all objects on the dashboard.
6. You can simply drag and drop to change the object order – just click and drag the ‘Gross Profit Vs. Prior/Target’ (Bar chart) above the other ‘Gross Profit Vs. Prior/Target’ (Line). Of course you’ll only notice the difference when both charts are visible – so give it a try to see how your charts show up on your reports.

Lock objects on your report

Do you always keep accidentally moving your charts / slicers and images? Lock objects on your report will leave them unmoved (even with MC Hammer’s “Can’t touch this” playing full blast – whooaaaa, amazing right!?)

It’s easy to activate, let’s see how…

From the ‘View’ ribbon, under the ‘Show’ group, select the check-box called ‘Lock Objects’ – to lock all the objects on your report canvas. Your charts will now be cemented into the canvas – nice!!

Rule-based conditional formatting for table and matrix

Now you can apply conditional formatting to your text on your tables and/or matrix visuals based on rules. I never really liked rules, even as a child – but these rules I don’t mind, because I can create them or break them – Muaahahahahaha!!! (Evil villain laugh)

Using our ‘Sales by Product’ report, lets see how we get the rule-based conditional formatting for your table or matrix going, just follow these easy steps:

1. Make sure you have either a table or matrix visual in your report.
2. Choose a value from the ‘Values’ list for which you would like to apply the rule-based conditional formatting – we’ll choose ‘Target Var %’. Click on the down arrow next to ‘Target Var %’.
3. Choose ‘Conditional Formatting’ from the list.
4. Select ‘Font color scales’ as the conditional option.

Once the ‘Font color scales’ have been selected, you will see a pop-up box appear with options to how you want to format your values.

Let’s see how this is done…

1. You’ll notice the default value here, which is the value we selected from the ‘Values’ list above.
2. By default the ‘Font color scales’ will use the value range scale, select the ‘Color by rules’ to open up the rule based selection.
3. Click ‘Add’ to add as many rules as you want, based on the values in your column / row values. Note in our example, I am entering the values as decimal numbers as we are working with percentages %.
4. Click ‘OK’ to apply your rules-based font color scales.

And there you have it, some pretty sleek looking conditional based rules for your font / text values!! This also helps an awful lot with quickly picking up on sales or products who have not performed well against the sales target.

Cell alignment for table and matrix

Now you can align your table and matrix visual column values auto, left, center or right, nice!!! Let’s use the same matrix visual and see how this works.

1. With the matrix or table selected, click on the ‘Formatting Pane’ to enter the formatting options.
2.  Open up the ‘Field Formatting’ list.
3. Choose the column for which you want to align the values for – we’ve chosen our ‘Target Var %’ column.
4. From the ‘Alignment’ option, choose from the drop-down list the alignment you wish to apply – we’ve chosen ‘Left’

Now see the corresponding column values align in the way which you selected.

 Report options for slow data sets

Do you have a Power BI reporting model which has a slow data source by nature? This option may help – it allows your model to send fewer queries when interacting with the report, making your report navigation and experience more efficient. I think this will only affect a small group of users with very large Power BI reports, and complex data source queries – let me know what your thoughts are in the comments below!!

To activate these features, use the following steps:

1. Click on ‘File’
2. Choose ‘Options and settings’
3. Choose ‘Options’
4. With the ‘Options’ menu open, click on ‘Query Reduction’
5. The first option ‘Reduce number of queries sent by’ allows you to disable cross-highlighting on all reports ensuring fewer back-end queries and a more efficient navigational experience.
6. The second option ‘Show and Apply button and only send queries once for’ – Slicer selections. With this option activated, all slicers on your reports will have an ‘Apply’ button which will only apply the filter context once the ‘Apply’ button has been selected. Just changing the filter context does nothing until you click on ‘Apply’. This reduces the filter queries sent to the report and again should increase the report performance.
7. The same applies with ‘Filter selections’ where the filter will only apply when the ‘Apply’ button is selected.
8. Click on ‘OK’ when you have made your relevant query reduction selections.

Esri Plus Subscription for ArcGIS Maps for Power BI

Since the beginning of the ESRI Map addition, I’ve always wanted more from their maps – well now we can!!! Richer mapping capabilities await you……

They’ve released a new subscription service called PLUS, this new service will help guys and girls like you and me do so much more with their geo-spatial data. Let’s have a quick look at some of the new additions.

• More street address geocodes – up to 5,000 data points per map
• Additional basemaps, including: National Geographic, Oceans, Imagery, Imagery with labels, Terrain with labels, Topographic, USA Topo Maps, and USGS National Map (USA)

• Living Atlas reference layers that help add more context to your maps

• World Demographics within the Info-graphics feature for categories like Education, Population, Income, or even Segmentation.

To start using Plus, all you need to do is sign up for a subscription or sign into an existing one through the plus button on the top right.

This service does come at a cost, but a small one at it, a mere $5 per user per month – not bad!! Remember that as with Power BI, the users you share the maps with also need to be paid PLUS users in order to see / use the maps you have created. You can learn more this new Plus subscription from Here. Of course the the free capabilities included with Power BI remain the same.

Filtering performance improvements

Power BI has made some updates in the way filtering is implemented. It now improves your performance for certain scenarios, in particular when you are using strings. They’ve also removed the 500 value limit on how many values you can match when filtering your data for all data sources except for analysis services models and live connections.

Data connectivity

Impala connector – support for Windows Authentication

As as a result of many requests from users, Power BI also released the support for Windows Authentication to the Impala connector. They are also planning to add support for Windows authentication to the On-premises data gateway, as well as Kerberos-based Single Sign-On support for the Impala connector via the Gateway. Schweet!!

So once you’ve specified an Impala cluster to connect to from Power BI Desktop, you can now select Windows as the authentication type in the Credentials dialog. Then from within the Windows authentication option, you can select whether to use the current windows user credentials or alternate user credentials.

Query editing

Add Column From Examples

Power BI seems to always strive to help us user do things quicker and more intuitively. This addition helps users with more scenarios to generate “Conditional Columns” as part of a button called “Add Column From Examples”.

Let’s look at an example below using our Sales Demo.

With the Query Editor Open, let’s have a look at our table called ‘Products’. We want to be able to add a new Location column based off the examples of another column, and then specify what each value should relate to.

1. Click on the ‘Add Column’ ribbon.
2. Select the ITEMLOCATIONCODE column from which we want to derive our location values.
3. Click on the ‘Column From Examples’ option.
4. Choose ‘From Selection’ – to choose the selected ITEMLOCATIONCODE column as example.

So now you will see a whole new experience from within Power BI Query Editor. First off we can see that Power BI has added a Custom Column, but it starts off with null values.

1. We know that the Location names we want to type should relate to a ITEMLOCATIONCODE.
2. So here we’ve typed some values, San Francisco for ITEMLOCATIONCODE = 4, Seattle for ITEMLOCATIONCODE = 2, Texas for ITEMLOCATIONCODE = 3 etc.
3. Click on ‘Ok’ to create the conditional column.

So what makes this so special is that from the four locations we’ve manually typed in, it has filled the rest based on the conditions that we have applied – see values in a. Notice also the logic for the condition on the top in blue, where it has automatically created a conditional if then else……now that is pretty cool.

Of course you can always still create conditional columns in the old way, but I quite like this way – much better experience!!!

Once the table has been created and loaded to the model, here is the result…..pretty cool right?

Analytics

Cell-level formatting for multidimensional AS models for multi-row card

While we won’t go into depth on this update, it’s awesome to know that this new feature allows cell-level formatting defined in your multi-dimensional Analysis Services (AS) to automatically flow through and be applied to your tables, matrix, single value cards, and multi-row cards.

And that’s it for the November 2017 update folks!!!

Now with this arsenal of new Power BI weapons, you can feel comfortable to be able to go up to your executives and say that you are “too legit to quit!!!” That’s right, do a little “Can’t touch this” dance and feel emPOWERED to take over the world!!

Until next time, keep safe and stay blessed!!!

For more great posts, follow our blog page.  To learn even more about Power BI, enroll in our training.