We have great updates this month! We are releasing new button actions, multi-column sort for tables, dual axis for line chart, a search for the filter pane, updates to decomposition tree, and much more! We are also releasing a preview of an enhanced dataset metadata feature, which will be foundational for XMLA read/write and for management of Power BI dataset and the migration of Analysis Services workloads to Power BI.
Check out the full blog to learn more about all the updates and enhancements this month.
Here’s the complete list of March updates:
- Multi-column sort for tables
- Dual axis for line chart
- Filter pane search
- New ribbon is now on by default
Check out the following video for a summary and demos of the major updates:
Important reminder –
Before we jump into the details of this release, it is super important to remember that next month, April 2020, the modern filter experience will automatically go live in all reports for all users in Power BI Desktop and Power BI Service. This also means that the old filter pane will no longer be available. So, if you have not upgraded any of your reports, we strongly recommend doing so this month, so you can tweak or customize any of the formatting before your reports get auto-upgraded in April.
Reporting
New action types for buttons
This month we’re excited to announce that we’ve added two new action types for buttons:
- Page navigation
- Drill through (preview)
Page navigation
Previously, when you want to create a button to navigate to another page you have to go first save a bookmark for your destination page, and then you have to create a button with a bookmark action that references that bookmark you saved. To help reduce the number of steps needed for you to create navigation experiences, in this release we’ve added a built-in page navigation action. With this new action you can quickly build an entire navigation experience without having to save or manage any bookmarks at all!
To set up a page navigation button, create a button with Page navigation as the action type, and select the Destination page:
With this new action you, can quickly build a navigation pane and avoid having to edit and manage bookmarks if you want to change which pages to show in your navigation pane.
Also, if you like the navigation experience used above, you can check out Chris Hamill’s blog site, Alluring Analytics, for this template and more reusable Power BI assets.
Drill through (preview)
In addition to page navigation, we are releasing a new drill through action as a preview. This action type will allow you to contextually navigate to a drill through page.
This feature can be useful if you want to increase the discoverability of important drill through scenarios in your reports.
In this example, after the user selects Word, the button will be enabled, and they can drill through to the Market Basket Analysis page:
To set up a drill through button, you will first need to have valid drill through page within your report. Then, you will need to create a button with Drill through as the action type and select the drill through page as the Destination.
Because the drill through button has dual states (when drill through is enabled vs. disabled), you will see that there are two tooltip options.
However, feel free to leave them blank to use the auto-generated tooltips, which are based on the destination and drill through field(s).
Here’s an example of the auto-generated tooltip when the button is disabled:
And here’s an example of the auto-generated tooltip when the button is enabled:
However, if you would like to provide custom tooltips, you can always input a static string.
You can also use conditional formatting to change the button text based on the selected value of a field. To do this you will need to create a measure that will output the desired string based on the DAX function: SELECTEDVALUE.
Here’s an example measure that will output “See product details” if a single Product is NOT selected; otherwise, it will output “See details for [the selected Product]”:
Once you’ve created this measure, you will select the conditional formatting option for the button text:
Then, you just select the measure you created for the button text:
So, here’s the result when a single product is selected:
Then, here’s the result when either no products are selected, or more than one product is selected:
The button will work like normal drill through, so you can also pass filters on additional fields by cross-filtering the visual(s) which contain the drill through field. For example, using Ctrl + click and cross-filtering, I can pass multiple filters on Store to the drill through page because my selections cross-filter the visual that contains Product (the drill through field):
So, when you invoke the drill through, you will see filters on both Store and Product being passed through:
Since the drill through button is not directly tied to a single visual, if there is ambiguity in your selection, then the button will be disabled.
In this example the button is disabled because there are two visuals that both contain a single-selection on Product, so there is ambiguity on which data point from which visual to tie the drill through action to:
We hope you enjoy these new button actions, and if you have any feedback for the team, please comment in our community post. It is greatly appreciated!
Additionally, if you liked the sample report used in the example, it can be downloaded from PowerBI.tips, where you’ll find a lots of other scrims and layouts.
Multi-column sort for tables
Multi-column sort for tables has been a highly requested feature, so we are super excited to bring it to you this month!
To add more columns to the sort order, Shift + click the column header you would like to add next in the sort order. If you Shift + click a second time on the same column, this will change the sort direction for that column. Furthermore, if you Shift + click a column you have previously added to the sort order, this will move that column to the back of the sort order.
For example, if you click Class and then Shift + click BrandName, then the table is sorted first by Class, then by BrandName:
Next, if you Shift + click on BrandName again, this will change the sort direction for BrandName:
Finally, If you Shift + click on Class again, this will moves it to the back of the sort order, meaning that the table is now sorted by BrandName first, and then Class. This will also reverse the sort direction of Class since the column had been previously selected.
Dual axis for line chart
We are happy to announce another highly demanded user voice item! You can now include a second Y-axis on your line charts, enabling you to plot two trends with different ranges along the same X-axis progression.
To use the second axis, drag fields into the new secondary Y-axis field well, and they will be drawn into the visual against a Y-axis on the right side of the chart. You can format these lines and this axis in the associated card of the formatting pane. Otherwise, the formatting will mirror that of your primary Y-axis.
Filter pane search
For a while now, we’ve supported filter card search, which allows you to search across the values within a field.
Now this month, we are adding support for filter pane search, which will allow you to search across your filter cards.
This feature is super helpful if you have several different filter cards in your filter pane and need help finding the ones of interest.
Additionally, you can format and tweak the search box, just as can with the other elements of the filter pane.
While this filter pane search feature will be on by default, you can also choose to turn it on or off by selecting Enable search for filter pane in the Report settings of the Options dialog.
One last awesome part about the feature is that it was worked on by one of our interns, Parker Robinson. He’ll be returning as a full-time developer, so you’ll see more of Parker’s work in the future!
Updates to decomposition tree
Bars per level formatting option
The decomposition tree now supports modifying the maximum bars shown per level. This is a formatting option found in the Tree card. The default is 10 and users can select values between 3-30. Setting a low number is particularly handy if you don’t want the decomposition tree to take up too much space on the canvas. It’s easy to cycle through the bars by using the up and down arrows in the visual.
Updates to behavior of data bar length
Previously, the data bar length was based on the values currently shown in the tree and not all the values in the range. If a user scrolled up or down the tree, the first data bar would return to the maximum. This behavior has been improved and the global maximum is now remembered when scrolling up and down the visual. In the example below, a user selects the down arrow in ‘Plant’ and the next batch of results is shown. The data bar for Plant #0477 is no longer full as its value is calculated based on the maximum value in the whole tree (Plant #0288).
New ribbon is now on by default
Starting this month, the modern ribbon will be on by default.
You’ll see a dialog announcing this change:
You can continue to give feedback on our community forum post. Keep in mind that this feature is still in preview, so you can find its toggle with the rest of the Preview features in the Options dialog.
There are also some new updates to the Insert tab in the ribbon. You can now insert the Power Apps visual and AI visuals directly from the ribbon:
To learn more about the changes to the ribbon, please visit our documentation.
Modeling
New DAX function: COALESCE
We’re excited to announce a new DAX function: COALESCE
The COALESCE function returns the first expression that does not evaluate to BLANK. If all expressions evaluate to BLANK, BLANK is returned.
Syntax
COALESCE(<expression>, <expression>[, <expression>]โฆ)
Parameters
Expression: any DAX expression that returns a scalar expression.
Also note that input expressions may be of different data types.
Return Value
This function will return a scalar value coming from one of the expressions, or BLANK if all expressions evaluate to BLANK.
Example #1:
EVALUATE { COALESCE(BLANK(), 10, DATE(2008, 3, 3)) }
- Returns 10, which is the first expression that does not evaluate to BLANK.
Example #2:
EVALUATE { COALESCE(SUM(FactInternetSales[SalesAmount]), 0) }
- Returns the sum of all values in the SalesAmount column in the FactInternetSales table, or 0.
This can be used to convert BLANK values of total sales to 0.
Visualizations
Waterfall chart by xViz
The waterfall chart by xViz is a powerful visual that displays the cumulative effect of sequential positive and negative values contributing to the final total.ย One of the most popular in the financial world, the xViz waterfall chart,ย delivers several top feature requests posted on Power BI Ideas, specificallyย for the waterfall chart.
Use cases:
- Variance analysis: drill down multiple levels to track absolute and relative variance across members
- Breakdown analysis: monitor each member’s contribution to the increase or decrease in totals (sales, revenue, etc.)
- Display running totals: track the updated summation each time a new member adds to the total
- Display multiple subtotals and total column: ability to display both sum or running sum for any set of members
Key features:
- Custom sort option: sort the waterfall with another column
- Breadcrumb for drill down scenario
- Switch between horizontal and vertical orientation
- Runtime deviation bar
- Axis break
- Semantic number formatting
- Summary table: tabular view of data
To learn more about the key features of this visual, check out the blog post.
Download this visual from AppSource or check out a sample report.
Updates to ArcGIS Maps
ArcGIS Maps provides you with the ability to build beautiful maps using your data within Microsoft Power BI. It works with all three versions of Power BI: Free, Pro and Premium.
This release we are happy to announce four key updates:
- New connection options
- Enterprise support is now generally available
- Support for multiple reference layers
- New table of contents
New connection options
With this release of Power BI, when you click on the ArcGIS Maps for Power BI visual within Power BI, you will now see three connection options:
- ArcGIS Enterprise
- ArcGIS Online
- Standard
The ArcGIS Enterprise and ArcGIS Online options are for users who have the premium app subscription and provides all the capabilities of Standard and extra capabilities, including additional geocoding, technical support, and access to mapping reference layers, and more. The Standard option is free and provides basic mapping capabilities.
Enterprise support is now generally available
In October 2019, ArcGIS Enterprise support was released as a public preview. In this release, we are happy to announce that Enterprise support is now generally available. This means that all ArcGIS Enterprise 10.7.1 and above users can connect to their organization’s Enterprise account and use their secure GIS data in dashboards and reports within Power BI. To get started, you would need the ArcGIS Maps premium app subscription, and then just connect to the ArcGIS visual within Power BI and start mapping!
Support for multiple reference layers
All premium app users can now add multiple reference layers to a single map visualization within Power BI. A reference layer is information represented on a map. It adds context to your operational business data. For example, let’s say you have mapped your store locations in Power BI. You can now overlay it against reference layers such as income, age, competitor locations or other demographics to gain valuable insights. You can add data and layers that are published and shared online by the ArcGIS community as well as layers from your ArcGIS Online or ArcGIS Enterprise organization.
New table of contents
We have also added a new table of contents that will help all ArcGIS Maps for Power BI users (free and premium) better visualize their data on a map. Now, when you drag data to a location field well and see it on a map, you can also see a table of contents that lists all the layers on the map and shows the features represented by the layers. This allows your report viewers to quickly understand the data that they are seeing.
Learn more about ArcGIS Maps for Power BI or check out their YouTube channel for video demos or tutorials on the different mapping and analysis tools within ArcGIS Maps.
Template Apps
This month we have several new additions to our Power BI templates on AppSource. With template app you can save time by connecting your own data to a prebuilt report that you can personalize and share.
Azure DevOps dashboard by Data Maru
Azure DevOps dashboard by Data Maru will help stakeholders to assess iteration health and get insight into development details.
Measurements will help evaluate:
- Current and historic iteration status (number of user stories and bugs, and breakdown of all tasks)
- Holistic view of current iteration (including current progress, critical items, and outstanding bugs)
- Work item by state
- Work Item assignment breakdown by user
With the details gained above, stakeholders will be able to understand iteration details and examine the data from many different angles. They can easily compare multiple iterations and combine details for the whole.
To use this template app:
- You must be a member of a project in Azure DevOps
- Set the View Analytics permission to Allow
- Enable the analytics views preview feature either for individual users or for the organization
- Boards must be enabled. If it is disabled, analytics views won’t be displayed
- The process of your project should be the agile process only
To connect to your data:
- Click Connect
-
Enter your Organization name and your Project name in Azure DevOps
- Choose OAuth2 for Authentication method and sign in with an account that has View Analytics permission for the project.
Download this app from AppSource.
TeamsPower by Encamina
TeamsPower allows to get a deeper knowledge about the status of digital transformation of your company, thanks to analyzing the use of your Microsoft 365 applications.
This report discusses which, how, and how much is the M365 tools used in your organization. As a result, it provides an analysis of the company’s digital transformation level. It offers the user a traffic light-style view that places the company on one level or another, based on configurable thresholds. If the company is above the set threshold, the indicator will have a green color, if it is in the average, yellow, and if it is below, it will appear in red.
TeamsPower analyzes three key indicators of digital transformation:
- Digital adoption: provides a detailed analysis of the use of Microsoft 365 applications.
- Digital intensity: indicates how much use is made of Microsoft 365 applications, based on the frequency of user activity.
- Digital maturity: shows how Microsoft 365 apps are being used within the company
How to connect to your data?
Once the application is installed, your data should be connected to TeamsPower by using your tenant ID and choosing a scenario to modify the target thresholds set in the application. After you connect your company data, the application will display your own information.
Download this app from AppSource or check out the documentation.
Data preparation
Query diagnostics is now generally available
We are happy to announce that query diagnostics is now generally available. With your feedback we’ve added the capability to diagnose single steps and improved the experience of using query diagnostics. Please look forward to a deep dive blog later this month.
Data connectivity
Hive LLAP connector
We’re happy to announce the public preview of the Hive LLAP connector. This connector provides both Import and Direct Query capabilities and the ability to specify Thrift Transport Protocol as ‘Standard’ or ‘HTTP’.
This connector can be found in the Other category of the Get Data dialog.
Cognite connector
This month, we’re also excited to announce the public preview of the Cognite connector. The Cognite Power BI connector enables data consumers to read, analyze, and present data from Cognite Data Fusion (CDF). CDF is a software package used to manage massive quantities of industrial data. Users can easily access data such as assets, data points, events, files, and time series, and then analyze and aggregate it. This connector can be found in the Other category of the Get Data dialog.
Other
We have a few more other updates this month:
- We are releasing a preview feature for enhanced dataset metadata
- Admins can now allow users to use default system credentials for web proxy authentication
- We are releasing a brand-new administrator in a day training
Enhanced dataset metadata (preview)
Power BI Desktop creates dataset metadata in PBIX and PBIT files. Until now, the metadata was stored in a format very specific to Power BI Desktop. The enhanced dataset metadata preview feature uses a standard format closer to that used for Analysis Services tabular models, based on the Tabular Object Model.
In the coming weeks, this will be a foundational feature because it will enable capabilities such as:
- XMLA read/write for management of Power BI datasets
- The migration of Analysis Services workloads to Power BI to benefit from next-generation features
You can turn this feature on in the Preview features section of the Options dialog:
Stay-tuned for the follow-up announcement in the coming weeks.
Using default system credentials for web proxy
Previously, web requests issued by Power BI Desktop do not use any web proxy credentials, so if you were using a proxy server, Desktop may not be able to make web requests. With this month’s release, system or network admins can allow users to use default system credentials for web proxy authentication.
The administrators can create a UseDefaultCredentialsForProxy registry keys to enable it, under:
[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft Power BI Desktop]
or
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power BI Desktop]
Then, the proxy settings, as you’d see defined in Internet Explorer, will get used when Desktop makes web requests as well. As with any change to proxy or credential settings, there are security implications to this, so be sure that your administrators have configured these proxies correctly before turning on this feature.
New instructor-led administrator in a day training
This month we are announcing a new administrator in a day training! This day of training will walk you through the Microsoft Power BI administration and the management of a Power BI tenant, including the configuration of tenant settings, usage monitoring, and provisioning of licenses, and other organizational resources. โIt covers the typical admin tools and tasks, such as Power BI admin portal and Office 365 admin center, and how to automate tasks by using administrative APIs and PowerShell cmdlets. Additionally, it includes hands-on labs specifically around using Premium capacity in Power BI, safely sharing Power BI assets, and finally using the Power BI audit log to optimize your Power BI usage.
The workshop assets are offered in English and split into two parts:
-
Instructor content for partners
Download the instructor content, which includes presentation decks, demo scripts and train the trainer material.
-
Attendee content
Download the attendee content, which includes the lab manual and datasets.
The content is offered in English and will be updated quarterly. We also offer otherย instructor-led training content for Power BI, so be sure to check out them out!
That’s all for this month! Please continue sending us your feedback and don’t forget to vote for other features that you’d like to see in Power BI Desktop. For any preview features, you can always give us your feedback in our active community forum. We hope that you enjoy the March 2020 update! See you next month!