Power BI March 2020 New Drill through Functionality

Currently we have lots of visuals on the report pages and the only way to know that they drill through to other reports is to hover over them

In the March 2020 release there is new functionality in the form of buttons

Add a blank button

This button is now set to drill through preview and you have chosen the drill through page. On normal drill through, everything this that is available is shown which is difficult when the report structure is complex. This allows you to target and manage the drill through structure.


Nothing selected
31 days selected

And this drills through to the detail reports for 31 days. Note that the tooltip in Desktop tells you how to drill through. Lets see this in Service.

Change the text on the button

Lets customise the button text using conditional formatting

the data item behind this drill through is ‘Resolved in’ and then it contains number of days categories.

Create a new measure

A new measure has been added to the dimension table containing the Resolved in Item

String for Resolved in Button logic = IF((SELECTEDVALUE('dim table'[Resolved in ],0)==0),"See Resolved in Details", "See Details for " & SELECTEDVALUE('dim table'[Resolved in ]))

If the selected value is null, set it to 0. So if its zero, show “See resolved in Details”. If its not zero then it can be displayed

Add New Measure to the Button

Resolving a noisy drill through with Drill through Buttons

This visual in this Drill though report has three Drill through’s for Incident. Then the stage of the incident. Finally the Business Property Drill through. All are valid but we would prefer to Not have the stage here because this should only be accessible via Incident.

Add two buttons. Link one to the Incident Drill through report. The other to the Business Property Drill through report

We want the button to be dark red when its active.

Create a new measure for the Incident in the incident Dimension

String for Incident in Button logic = IF((SELECTEDVALUE('dim BPIncident'[Incident Ref],0)==0),"See Incident Details", "See Incident Details for " & SELECTEDVALUE('dim BPIncident'[Incident Ref]))

And add this as conditional Formatting to the button Text

Create a new measure in the Business Property Dimension

String for Business Property in Button logic = IF((SELECTEDVALUE('dim BusProperties'[BP Number],0)==0),"See Business Property Details", "See Business Property Details for " & SELECTEDVALUE('dim BusProperties'[BP Number]))

And add this as conditional Formatting to the button Text

And now you should get drill throughs that the user can immediately see and use. Avoiding the stage drill through because it doesnt match what we want the journey to be

Note that the buttons are greyed out

Weaknesses

  • The button takes up more space.
  • Currently you have the drill through button and the drill through on the visual. This could be confusing to the user. It would be great if you could turn the drill through on the visual to off

Power BI Drill through to another Report

I have created two very basic test reports to work with to simplify the process

they both use the same table of data

Report 1

Page 1 Is just filtered on year and has a few visuals. 

It contains column charts with month on the Axis

Page 2 is a basic table

Drill through field is Month and Cross report is switched on On

Report 2

Page 1 is Filtered on Year and Region and has a few visuals including a column chart with Month on the Axis

Report 2 is the source because we will be drilling through from here

Report 1 is the Target because we are drilling through to page 2 from Report 2

Cross Report Drillthrough Toggle

Cross Report is set to ON for the Source Report (As above)

Leave Cross Report Set to Off for the Target Report

Report Setting Options

Both reports are Set File > Options and Settings > Options > Report Settings

Cross Report Drill through – Allow Visuals in this report to use Drill through Targets from other reports

Both reports are published to a workspace.

Once in the Service I create a very simple dashboard from a visual from each report as my starting point

here is where it goes wrong

Start at the Dashboard and go to region. click on the Total from region report Tile

Once in here I right click and get the drill through on the Other report, So far so good. I click this and go to the drill through

Note that the filters are kept as expected from the region report.

Now I decide to go back to the dashboard because I want to go to the main report. 

In this case I click the back button (Because the back button doesnt work on the report when you move between reports)

Or I can simply click on the  Bread crumb trail of POC reports and Dashboards and go back to the dashboard this way

This time I want to go to the main report with just the Date slicer.

And this is where the problem lies. Notice that the filters from the drill through are still applied so this year report is filtered by Month and region.

When moving back to the start we don’t want the drill through filter applying. If you drill through in the same report this works absolutely fine. You can go back and the filters are unapplied. However they arent unapplied when drilling through to other reports.

The Solution

 For each report if you go into File > Options and Settings > Options > Report settings and make sure Persistent Filters is ticked ‘Dont allow end users to save filters’

Power BI July 2019 Updates

Each Month Power BI Releases an update and we like to go through the updates and come up with our favorites of the month (And those updates that aren’t as successful)

Reporting

Filter Pane

The Filter pane has been updated and filters have been removed from the Fields pane. This is great because it was slightly confusing to drag and amend filters in ‘Fields’ but then see them in the Filter pane. Now you drag the field straight to the filter pane

You can also rename Filter headers which makes them much more useable.

If you go to Options and Settings > Options > Report Settings, you can also tick Allow Users to Change Filter Types.

This means the user can choose between advanced and basic Filter Pane. This is great if you want more filter usability for the user

There are still issues for me when it comes down to the new Filter Pane and Slicers. This could be confusing to a user. If Edit interactions was added to Filters, then many slicers could be removed from reports ensuring more screen time for visuals.

Conditional Formatting for Visual Titles

I click on Doncaster and the visual Changes but the Title Remains the same. I want it to dynamically include the selected region.

There is a button by Title Text with three dots showing that there is a hidden menu.

Here you can select a measure that you have created containing the title you want, rather than use the standard title

Sentiment Score Title = “Percentage Sentiment Group for” & SELECTEDVALUE(Tweet[Location])

Previously, you can create your own headers for visuals, but they were simply hard coded. Here is my visual for Sentiment Group. I also have a Region Filter on the page.

All my Reports are filtered on Months so now each visual can contain this information.

Conditional formatting for web URL actions for buttons shapes and images

Finally, we can now change and image depending on a dynamic measure. This is one of my favorite new features. For example, this is fantastic for showing conditional image for feedback, Positive, Negative, Up Down etc.

This work the same as visual titles above

Even better, we should be getting an update soon where you can add the DAX straight into the Title Text rather than needing a separate measure.

My conditional visual for this new feature would be….

Analytics

Drill through across reports

For me, Drill through is one of the most important concepts behind Power BI and its not yet fully functioning. For example, all the market place visuals don’t have this ability. It’s really frustrating to find the perfect visual and then realise you can’t drill through to further information.

This new functionality gives me some hope that we might see future fixes to this.  We can now set up a navigation experience across reports.

Start with the target page (the page you want to land on from other reports) and set Cross Report Toggle to on.  

You also need to then turn cross report on for the reports that you drill through from. You can do this as above or in Service / Reports

If for example I’m drilling through by Region. I right click on the Region Data Point in a visual and I can see the drill through Report [Page Name and Report]

This is going to be incredibly helpful when I want to divide larger reports into smaller ones (With less data in each)

Key Influences visual supports continuous analysis

The Key Influences visual was one of my favorite updates a couple of months ago and I have used it to support Twitter sentiment grouping. (Negative, Positive, Neutral)

Previously I could only analyse by category but now we can analyse a continuous numerical field. To do this set Analysis Type to Continuous.

We can now analyse data items like sales amount.

This new visual has already proved really popular with users and its good to know its improving all the time.

Partial Synonym matching for Q&A

Another really great feature. When a user is asking questions in Q&A, even if they can’t remember the exact phrasing, Q&A will give them suggestions.

Data Connectivity

Lots of new data connectors. Power BU Dataflows, Oracle Essbase, PDF Connector, Intersystems IRIS, Indexima (Big data), Luminis InformationGrid, Solver BI360 and Paxata.

Its doubtful that I will be needing these connectors currently but it’s great to see new connectors being added every month. Its worth noting that the on premises data sources will need the new release of the April On Premises Data Gateway which should be coming soon.

Web by Example Connector – Allows you to scrape data from HTML Pages and is now even smarter. This is currently in preview and I can’t wait to see how it works.

Data Preparation

Data Profiling enhancements

In Power Query Editor New Profiling enhancements are available to check your data

In View > we have Column Distribution, Column Profiles column quality.  Try ticking these options for discover lots of information about the quality of your data.

This is such a good feature when you are starting to add and understand your data. A quick look at my Sentiment group shows I have nothing outside of Positive Negative and Neutral. I can quickly see any issues that may need resolving.

Fuzzy Merge Feature

Another smart preparation feature which helps with merging records. I haven’t needed to use this yet but its good to know that this also speeds up merging of records.

M Intellisense supported in Formula bar

If for example you go to Add Column, Custom Column. You can type in your custom code and Intellisense will provide lists of functions etc. to help you. We didn’t have this before in Power Query mode, so this will really enrich the experience.

This has been such a good month for Power BI. I am excited about being able to create custom headers and visuals and I can’t wait to try the updated Key Influences Visual.

Create your website with WordPress.com
Get started