COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
The March 2020 update to Power BI desktop is a full one, as this blog shows!
Part four of a eleven-part series of blogs

New goodies in this month's update include the roll-out (sort of) of the new ribbon, new actions for buttons, multi-column sorting and a second axis for line charts.

  1. The March 2020 Power BI Desktop Update
  2. The new ribbon is live (sort of)
  3. Page navigation buttons
  4. Drill-through navigation buttons (this blog)
  5. Multi-column sorting
  6. Searching in the filter pane
  7. Line charts can now have a dual axis
  8. The new DAX COALESCE function
  9. Changes to ArcGIS
  10. Query Diagnostics
  11. Generating embed codes is now turned off by default

For a cumulative list of all of the updates to Power BI Desktop in the last few years see this blog, or have a look at the Power BI courses that we run.

Posted by Andy Brown on 14 March 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Drill-through navigation buttons

This is an excellent new feature, although for some reason it's marked as being in preview:

Preview drill-through

I don't get why this is a preview feature. I haven't enabled it, and it's not deselectable in the options dialog box.

 

I've even published this to check that the feature works in Power BI Service - it does.  Because this is such a useful feature, and because it seems to work perfectly and be fully integrated into Power BI, I've decided to include it in this blog.

A quick drill-through refresher

So here's a quick reminder of what drill-through is, and how to set it up.  First create a page listing information for a parent table (here I'm showing sales by UK regions):

Regional data page

I want to be able to drill through to show sales for the towns in any region.

 

Next, create (and hide) a separate page to show sales for the children for any parent:

Towns table

Here I've also added a dynamic title using a measure, but to keep this blog to a manageable length I haven't included how to do this here.

With this second page selected (but not the visual on it), drag the parent field onto the page's field well:

Drag the region name field

Drag the region name field onto the part of the field well which says Add drill-through fields here.

This creates the following:

Drill-through field

We've successfully assigned the RegionName drill-through field to this page.

 

Now you should be able to right-click on any region, and drill through to show the data for its towns:

The drill through menu

The drill-through menu should appear when you right-click.

Creating a drill-through button

The problem with this feature has always been this: how does a user know it's there?  The new drill-through action allows you to create a button:

Adding a button

Choose to add a button, then add text to it.

 

You should now have a clickable button:

Clickable button

The button now says the right thing; now we need to make it do something.

 

With the button selected, change what it does in its Action card:

Drill through button Go to child data
Choose drill through ... ... then choose a page.

The clever thing now is that the button will only be clickable when a single region is selected:

Multiple regions Single region
No single region selected Single region selected

And if that was all that this feature allowed you to do, it would still be worth it - but read on!

Customising the button tooltip

With the action button you've created selected, you can choose what tooltip to display:

Enabled and disabled tooltips

Here I've chosen to display a different tooltip if a region is selected (and hence if the button is enabled).

 

Here's what I'd see, for example, if I have a single region selected (and hence if the button is enabled):

Tooltip for enabled button

The enabled tooltip shows up.

 

If you don't add your own tooltips, Power BI will use its own automatically generated ones, displaying different messages according to whether the button is enabled or not.

Further customisation

However, you can take things much further by creating a measure to customise the button's caption.  First create a measure similar to this:

SelectedRegion = IF(

// if there isn't a single selected region name,

// this will return the alternative value (0)

SELECTEDVALUE(Region[RegionName],0) = 0,

// in this case, show instructions for

// what to do

"Select a region",

// otherwise, show the region name

"Show data for " & SELECTEDVALUE(Region[RegionName])

)

(If you don't know how to create measures, this is probably a step too far in your Power BI learning at the moment). 

Now go to the button's formatting properties, and set custom button text:

Setting button text

Right-click on the Button Text property to set conditional formatting.

 

Set your text to show the value of your measure:

Selected region

Set your button text to be the measure you've just created.

 

Things now look more professional!

Button text for region

If you select a single region, you can see this on the button's text.

 

You can apply conditional formatting to the button's font, fill and outline colour, among other properties, to make your button look completely different according to whether a region is selected or not

This blog has 0 threads Add post