Power BI Desktop Update – January 2018

My New Years Resolution: A healthy Power BI Diet!!

Hi everyone, and welcome back!! Hope you all had an amazing festive and holiday season and have come back restful and ready to learn!! If you were anything like me, you’ve completely overindulged in everything you could get your hands on!! But, I have good news. Although you may look / feel a couple of pounds over weight, indulging in this Power BI Desktop Update – January 2018 will leave you feeling, well….light and more powerful, almost like Iron man, to be exact :-)

So what does Power BI have for us? This new year’s surprise from Power BI leaves us zooming into the year with supersonic speeds……….wwwoooooooooossssshhhhhh!!!

Reporting

• Show and hide pages
• Control data label background color for Cartesian and maps visuals
• Increase area used for axis labels in charts
• Bar/column padding control
• Show dates as a hierarchy (preview)
• Add an anchor date for relative date slicer
• Top N selection in Q&A

Analytics

• Correlation coefficient quick measure

Custom Visuals

• PowerApps
• TreeViz
• Funnel with Source by MAQ Software
• Box and Whisker chart by MAQ Software
• Agility Planning Matrix Light
• Image Grid
• Gantt Chart by MAQ Software

Data connectivity

• Support for Azure Active Directory authentication for Azure SQL Database & Data Warehouse connectors

Other

• Advanced language settings for the Windows store app

_______________________________________________________________________

As you can see, this Power BI menu includes some delicious, and healthy recipes (features).

Let’s have a look at the ingredients shall we?

_______________________________________________________________________

Reporting

Show and hide pages

One of the things Power BI have included this month, is the ability to hide report pages – like an iron man stealth mode, cooool!! In our example, I want to show that even though some reports are hidden, does not mean that we can’t use / see them. This will become particularly useful with drill-through reports etc.

Lets start with how to select / mark a report as hidden:

1. Right click on the report you would like to hide.
2. Select ‘Hide Page’ from the pop-up menu.

Ok, so you’ll see once it’s hidden in Power BI Desktop you will still see the report, but notice it is now grayed out. Once published, you won’t see this tab anymore, but we’ll get to that in a moment.

In order to show the usefulness of this, I’ll be using my ‘Sales Overview’ report to drill through on my sales by category graph and then show only the sales by ‘Accessories’ but from my hidden ‘Monthly Trends’ report. We’ll start by setting up the drill-through by adding ‘ITEM CATEGORY’ as a drill-through filter on the now hidden ‘Monthly Trends’ report.

Once done, let’s test the drill-through by following these steps:

1. Go to the ‘Sales Overview’ report.
2. Right click on the ‘Accessories’ column on the chart.
3. Scroll your mouse over the ‘Drillthrough’ option.
4. Select ‘Monthly Trends’.

So even with the hidden report, the drill-through selection jumps to the hidden ‘Monthly Trends’ report, filtered by ‘Accessories’ – cool right!!!

When we carry the same tasks out but on the published report, notice once the drill-through has been applied, that you can no longer see the ‘Monthly Trends’ tab, even if you drilled through to that page. You can access that report when in ‘editing mode’ – should you want to make some adjustments after it has been published to the cloud. You can also use all features with these reports, such as pin objects to a dashboard, use bookmarks etc. Only difference is that you won’t see the report once published.

The show and hide functionality is of course very useful for other things too, such as keeping your published reports tidy by hiding reports that are a work in progress or hiding reports that fulfill any administrative function and which you would like to keep hidden from users.

Control data label background color for Cartesian and maps visuals

Power BI have added some additional formatting options to the data label backgrounds for Cartesian and map visuals. This feature although not quite as cool as going into stealth mode, give users the ability to adjust the backgrounds colors of their data labels, improving readability – reducing the effort it takes from users to see the necessary data.

Below I have a “before” chart without any of the formatting applied to the data labels – a little boring right :-)

By adding the label formatting, we can see a huge improvement in the way it’s visualized – let’s see how this is done:

1. Start by selecting the graph / chart for which you want to apply the formatting.
2. Click on the ‘Formatting Pane’ icon to access the format settings.
3. Switch ‘Data labels’ to on – if not on already. Notice the wide range of label formatting you can apply (text color, display units, decimal places, orientation, position, text size and font family) – I’ve changed by text color to white.
4. Click on ‘Show background’. A new menu will appear, giving you the option to choose your label background color as well as adjust the transparency. I’ve selected a dark purple to make my white text pop!

Awesome!!! Doesn’t it look better already? Now apply this to the rest of your charts / graphs.

Increase area used for axis labels in charts

Ever had the annoying issue with the axis labels not showing completely – yeah, this one has been a bit of a pain sometimes. Especially if you had long axis labels on your charts. Luckily our friends at Power BI always listen to their users, so this is now a problem of the past.

To get these pesky little guys sorted out, follow these steps:

1. With your chart selected, click on the ‘Formatting pane’ to access the settings.
2. On this chart we want to edit the y-axis labels, so click on the ‘Y-Axis’ settings.
3. From the ‘Maximum size’ option, increase the % to fit your text. Default it’s set at 25%, I only needed to set it to 35% for it to resolve my problem.

Bar/column padding control

Going hand-in-hand with the previous section, notice also the settings in blue. The ‘Minimum category width’ changes how thick your bars are. The ‘Inner padding’ adjusts how much space should be between the various categories. Adjust the ‘Inner Padding’ to see the difference it makes to your charts.

1. By reducing the ‘Inner padding’ of my chart, you can see the difference it has made compared to the chart above. It has brought the categories closer together – essentially saving me some chart space.

Show dates as a hierarchy (preview)

Power BI has made life a little easier this month, by showing us the default date hierarchy in creates for each date column. Before you wouldn’t necessary know what hierarchy Power BI generates for you, but now, you can see it for all date columns.

What makes this update useful for me, is that now you can either use the whole hierarchy, or just part of it, without scrolling through or adding and removing the ones, you want, or don’t want.

Below I have created two sales charts, one with the whole date hierarchy included, the second, I only dragged the year over – and voilà!!

Add an anchor date for relative date slicer

Remember the addition of the ‘Relative Date’ slicer – that was a welcome one!! But like a delicious homemade Power BI soup, it was missing an ingredient. Like in the example below, if you had a set of data and the data ranged from say 2010 to 2015 and you were in 2017, applying a filter for the ‘Last year’ would not give any results, as last year has no data. This month’s update is that missing ingredient – lets throw it into the pot!!

1. To start, select your relative date slicer.
2. Click on the ‘Formatting Pane’
3. Select ‘Date Range’ to open the menu.
4. Type in the anchor date for which the relative date slicer should apply or use the date picker.

A few things to note here: If your anchor date is 2016/01/01 and your slicer has been set to ‘Last 1 Years’ – it uses the anchor date, minus the filter, i.e. 1 year starting on the 1st of January and will then show us the data for the previous year. This filter context will change based on the slicer values you’ve selected – for example, if you set the relative date slicer to ‘Next 1 Years’, you will need to change the anchor date, as there is no data for the 2016 year – so choose how you apply this filter wisely.

Top N selection in Q&A

Last month, Power BI gave us the super duper capability for creating Q&A reports in Power BI Desktop. This month they bring some additional super powers, by including TOP N as part of the Q&A.

To get started with this magical feature, follow the steps from the December blog on Q&A here. Once the Q&A is up and running, start typing…….in the two examples below, we use the text “what are my top 10 customername by line total excl” and “what are my bottom10 customername by line total excl” 

Notice that we write normal English here, i.e. natural language and use the column names as reference. Also notice that we do not use measure names in the Q&A as this does not work.

Analytics

Correlation coefficient quick measure

For all you statistical dudes / dudettes out there, this month’s update includes a new quick measure – this quick measure calculates the Pearson correlation coefficient between two measures within a certain category – nice!!

To get started, use the following steps:

1. From the ‘Home’ tab, under the ‘Calculations’ group, click on ‘New Quick Measure’.
2. Open the drop-down list.
3. Scroll to the ‘Mathematical operations’ group and select the ‘Correlation coefficient’ option.

With the Correlation coefficient option selected, a few variable are required to get the quick measure to jump into action.

1. This is the calculation required selected from the drop-down.
2. Choose the Category for which you wish to create the correlation. We want to do this for ‘Customers’.
3. Choose the x-axis measure. We want ‘Sales’.
4. Choose the x-axis measure. We want ‘Quantity’.
5. Click ‘Ok’ to create the quick measure.

Here is the dax formula it created for us:

Sales and Quantity correlation for CUSTOMERNAME =
VAR __CORRELATION_TABLE = VALUES(‘Sales'[CUSTOMERNAME])
VAR __COUNT =
COUNTX(
KEEPFILTERS(__CORRELATION_TABLE),
CALCULATE([Sales] * [Quantity])
)
VAR __SUM_X = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([Sales]))
VAR __SUM_Y = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([Quantity]))
VAR __SUM_XY =
SUMX(
KEEPFILTERS(__CORRELATION_TABLE),
CALCULATE([Sales] * [Quantity] * 1.)
)
VAR __SUM_X2 = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([Sales] ^ 2))
VAR __SUM_Y2 = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([Quantity] ^ 2))
RETURN
DIVIDE(
__COUNT * __SUM_XY – __SUM_X * __SUM_Y * 1.,
SQRT(
(__COUNT * __SUM_X2 – __SUM_X ^ 2)
* (__COUNT * __SUM_Y2 – __SUM_Y ^ 2)

With the new measure created, drag the measure onto the canvas and view as a card. I’ve also added a scatter chart to show the relationships between these selected variables.

I don’t know about you, but this quick measure saves you a heck of allot of time!!!

Custom Visuals

While I don’t deal with these super exciting custom visuals included in the Power BI Update this month, be sure to check it out over here. Some awesome new visuals from MAQ Software and other.

Data connectivity

Support for Azure Active Directory authentication for Azure SQL Database & Data Warehouse connectors

So some exciting news for many users – Power BI has now given you the ability to use your Azure Active Directory authentication for both Azure SQL Database and Data Warehouse connectors. From now on there will be a new Microsoft sign-in dialogue – to get you connected to these sources with your AD account, quickly and easily – awesomeness!!!

Other

Advanced language settings for the Windows store app

More great news for users who use the Power BI Desktop – Windows Store App. Normally the display language is determined by your default windows display language setting, but if you want if you want it to be different than the windows setting – you can now choose a separate display language from your windows display settings. You can also choose to separate your model language from your display settings – so if you want to you can have your default windows setting in Spanish and your desktop display settings to be in French, you can now do this.

Hey this could be like a French / Spanish cuisine mix, classic French with a spicy twist – arriba, arriba!!!! “Go on! Go on! Up! Up!”

_________________________________________________________

And that is it folks!! I think this month’s Power BI ingredients are making a remarkable difference, both in taste and health – what do you think??!!

So go on, start that Power BI diet – your waist and clothes (and husband or wife) will thank you for it!!

Go here for more great blogs from the DataBear team.  Attend our training to learn more.

Until  next time, keep safe and stay blessed!!