Power BI Desktop Update – April 2019

April has another exciting release for the Power BI Desktop Update. Power BI is really making sure that they continuously improve on the entire Power BI experience and functionality. The one very useful feature that was added in this months release is the conditional formatting for visual titles. Visual titles will update according to the data selected. Power BI’s Machine Learning capabilities are also constantly improving. Before the release of Power BI Desktop Update for April, the Key Influencers visual could only support Categorical analysis to find key influencers. The analysis function is enhanced so that a continuous analysis can be run on numeric fields in order to find the Key Influencers that cause that field to increase or decrease.

For last month’s update, please see this link.

Here’s the condensed list of the Power BI Desktop Update for April:

Reporting

  • Filter pane improvements
  • Support for full filter pane editing
  • Ability to rename filters
  • Conditional formatting for visual titles
  • Conditional formatting for web URL actions for buttons, shapes, and images

Analytics

  • Drillthrough across reports
  • Key Influencers visual now supports continuous analysis for numeric targets
  • Partial synonym matching for terms in Q&A

Modeling

  • New DAX function – ALLCROSSFILTERED

Data preparation

  • Data Profiling enhancements and general availability
  • Fuzzy merge performance enhancements & general availability

Reporting

Filter experience improvement 

The filter experience in Power BI sees quite a big change this month. Power BI has made a lot of changes this year already to filtering by the addition of the filter pane, visible on the right-hand side of your report page.

The filter pane now becomes your one-stop shop to add all the fields that a report gets filtered by. There is no longer the need to change the filtering of visuals or report fields. This is now directly added to the filter pane.

In the newly updated filter pane, you can add and remove fields that are used for filtering, change the filter state, control the visibility of the pane and filter cards, and lock filter cards. Another functionality of the filter pane is that it scales with your report page and visuals to ensure optimal space utilization on your page.

 

Ability to rename filters

Visual titles are conditionally formatted based on selections

The new Power BI Desktop Update has introduced a new functionality where the Title of a visual can dynamically update based on your filter selection. In order to achieve this, we will have to make use of a DAX expression that is included in the Title of our visual.

In order for this functionality to work a new field must be created that will be included in your Title. This field will then update based on the field selected. Just a note here is that this field must always be a text field for this to work. For this example we want our Title to update based on our gender filter. See below DAX expression that we will use for our visuals Title:

Stacked Bar Graph Title = “Total Sales by Month for” & SELECTEDVALUE(‘Sales Male vs Female'[Gender])
To enable this feature to update the Titel of a visual, head over to the formatting pane and select Title. Hover over the Title, where three vertical dots will appear.
Click on the vertical dots and select Conditional Formatting.
This opens the following window where a field can be selected that will enable the Title to be updated. Let’s select our created field called “Stacked Bar Graph Title”.
Once this is completed the Title of the below graph gets updated based on the selection of the Gender filter.
This same functionality can be applied to web URL’s where you can dynamically update the URL based on your current selection.

Analytics

Drillthrough across reports

In the past, Power BI allowed accessing other pages in a Power BI report. The function, however, was limited to pages of the same report. Power BI has extended this feature allowing the functionality to reference other reports in a given workspace, removing the limitation access pages only in the same report. This is a powerful feature if you would like to link different reports together. Note here that the Workspace that we are referring to is in Power BI Service, thus this functionality will be available when the report is published to the Power BI Service.

In order to enable this functionality, there are two changes that are required to be made to your Power BI Desktop report.

  • Set up a drill through target page to be accessed from other reports within a workspace
  • Allow a report to opt into seeing drill through pages outside of the report

Firstly, a drill through target page is created. The functionality is found under the drillthrough section of the visualization pane. Here the Cross-report toggle must be turned on.

Secondly,  visuals in this report must be activated to use drillthrough targets from other reports. This setting is under the Report settings for the current file section of the Options dialogue.

The Cross-report drillthrough can also be accessed and changed from Power BI Service. In order to do this go to the workspace where your report is stored and click on the settings icon.

A pop-up will be displayed where you can scroll down to activate or deactivate the feature.

Once you’ve done that, any report can see the cross-report drillthrough pages within its workspace or app. In order to drillthrough to another report, you are required to right click on a visual and this will show you the drillthrough page from another report. What is really important when setting up the drillthrough functionality is that the fields in the ‘base’ report need to match the drillthrough fields set up on the target page. The matching also is required to be exactly the same by both the table name and column name,  but it may come from different datasets.

Key Influencers visual now supports continuous analysis for numeric targets

Before the release of the Power BI Desktop Update for April, the Key Influencers visual could only support Categorical analysis to find key influencers. For instance, what were the influences for a customer to give a rating of eight? The analysis function is enhanced so that a continuous analysis can be run on numeric fields in order to find the Key Influencers that cause that field to increase or decrease. The function is working for numeric columns, where calculated fields are included. Note here that measures are not yet supported to be included in analysing for Key Influencers.

Let’s give a quick overview of how to activate this function. Firstly, populate the fields in your Key Influencers visual.

Secondly, go over to the formatting pane and open the analysis tab, here you are able to select between categorical and continuous.

The result of your key influencers visual will be as below:

Note the following aspect of how the visual determines what factors influence an increase or decreases of a certain field. The Key Influencers visual run linear regression analysis in the background. The regression analysis will then rank all the factors that were included in the ‘explain by’ field to determine the potential influencers. The analysis will provide a result that explains how much an explanatory factor increases or decreases the average of the metric being analysed.

Power BI has done quick a good write up on how the Key Influencers visual work if you want to learn more about the functionality of this visual.

Partial synonym matching for terms in Q&A

The very useful and popular Q&A functionality is improved even more through the added functionality of synonym matching. This is especially relevant when you type a word that is part of a synonym of a field or table. The Q&A functionality will show the synonym in the list of suggestions.

Power BI Desktop Update – April 2019

Data preparation

Data Profiling enhancements & general availability

Data profiling in Power BI is a very useful feature when uploading data into Power BI through Power Query. The data profiling functionality allows you to get key insights into the data quality imported into Power BI. This is a feature that I greatly advocate for users to make use of as it is easy to identify if there are some major issues with your data and can spare you a lot of frustration in future.

The Column Quality bar and Value Distribution histograms functionality are already available and this months release sees the addition of the Column Profiles pane. The column profiles pane allows deeper profiling capabilities for any column of your data that is imported into Power BI.

  • Column statistics – number of errors, empty, valid, duplicated and unique values. Value distribution measures such as Min, Max, Average, Median, etc.

  • Column distribution – When selecting a column you will open a focused view of the inline value distribution histograms, also including the ability to Keep or Remove values, which will generate the corresponding Filter Rows step in your query.

In order to set up your data profiling view, you need to head over to the view tab in the Power Query tab. There you will find the following data profiling options:

  • Column Quality
  • Column Distribution
  • Column Profile

For the focused view you also have a very neat ability to view the data for a selected first 1000 rows or for the entire column. This option can be found in the bottom left corner of the query view.

ALLCROSSFILTERED ( <TableName> )

ALLCROSSFILTERED can be used only as a CALCULATE modifier and cannot be used as a table function.

This function removes all the filters on an expanded table (like ALL) and on columns and tables that are cross-filtered because of bidirectional cross-filters set on relationships directly or indirectly connected to the expanded table.

Fuzzy Merge performance enhancements & general availability

Power BI allows you to merge data from multiple tables from the Merge Queries functionality. There are in essence two ways to merge two tables, firstly, you can merge tables through an exact match of a column in each table and secondly, the fuzzy matching option that matches columns values that are very similar to each other.

The options in the fuzzy merge option are as follow:

  1. Similarity Threshold – This allows you to indicate how similar two values need to be in order to match. The minimum value of 0.00 ensures that all match each other, and the maximum value of 1.00 will only allow exact matches.
  2. Ignore case – This option allows text values to be compared through a case sensitive or insensitive setting. The default behaviour is case insensitive, meaning that it will ignore case sensitivity.
  3. Ignore spaces – This option will determine if text values should compare spaces or not, for instance, “Supply Chain” vs. “SupplyChain”. The option will default to ignoring spaces.
  4. The maximum number of matches – This option will allow you to specify how many matches a row may have. If you indicate that you want 1 matching row there will only be one match for a specific row.   The default behaviour is to return all matches.

This month we’re very glad to announce that Fuzzy Merge is now generally available.

That is it for this months Power BI Desktop Update. Make good use of all the new and updated functionalities, looking forward to next month bring the new updates and features that Power BI will have.