This month has many updates to previously released features, including an upgrade to tables to add the great features our new matrix, two new quick measures, and a new way to create bins. You’ll also now be able to take advantage of a new slicer type that lets you filter your reports down to relative dates, such as last 3 months. We are also very excited to announce report level measures for live connections to Analysis Services tabular models & Power BI service datasets.
Continue reading to learn about all these great features and make sure to register for the 2017 Microsoft Data Insights Summit to see all the new and exciting Power BI features in person!
Here’s the complete list of May updates:
- Relative date slicer (preview)
- New table visual (preview)
- Combo chart data label enhancements
- More URL support in table & matrix
- Mailto links in textbox
- Report level measures for live connections to Analysis Services tabular models & Power BI service da...
- 2 new quick measures (preview): Total for category & Rolling average
- Bin by count
- New connector: Dynamics 365 for Customer Insights
- Combine files – ability to reference “First File” as example
For a summary of the major updates, you can watch the following video:
Report view
Relative date slicer (preview)
In our continuing quest to add more slicer types, this month we are previewing a relative date slicer, which lets you filter based on the last 1 or more years, months, weeks, or days. This makes date slicers much more powerful, as you can always filter your report to the latest data.
You can choose the Relative option from the list of available date slicer types.
Once you select relative from the list, you will be able to specify the period to filter by. We have seven options to pick between:
- Days
- Weeks
- Weeks (Calendar)
- Months
- Months (Calendar)
- Years
- Years (Calendar)
If you pick an option marked with (Calendar), the filter will be based on calendar periods.
For example, if you filter to 3 years, data from the last 3 years from today’s date will show.
If you filter 3 years (Calendar), data from the last 3 completed calendar years will show.
We show the dates used for filtering under the slicer, so you always know what data you are looking at.
You can also switch to filter to this period or the next period.
By default, the date range includes today, but you can override this in the formatting pane for the visual:
This is useful if your data hasn’t refreshed today and you don’t want to include data from incomplete days.
We’re planning to incorporate this relative date approach into the Filter pane in a future update as well.
Turn on this feature through File > Options and settings > Options > Preview Features > Relative date slicer.
Find more details about the relative date slicer in the following video:
New table visual (preview)
We are very excited to release a preview of a new table, with many of the new features you love in the matrix preview. This new table, just like the matrix preview, is its own visual so you can test it out without needing to affect any production reports.
This new table has the same features of the original table, with the additional features of:
- Cross-highlighting
- Word wrapping
You can turn this preview on using the same option as the matrix preview, File > Options and Settings > Options > Preview features > New table and matrix visuals.
Find more details about the table preview in the following video:
Combo chart data label enhancements
We’ve add many data label improvements to line and column charts over the last several months, and you can now take advantage of these changes in combo charts well.
You can now:
- Change the orientation of your column labels to vertical or horizontal
- Move your column labels to inside base, inside center, outside end, or inside end
- Change the label density to show more or less of your line and column labels
Find more details about the combo chart enhancements in the following video:
More URL support in tables & matrix
We now support new link types in the original and preview table and matrix:
- mailto
- News
- Telnet
- FTP
- File
If you have one of these link types in your data, and mark the column’s data category as Web URL, we will open the application you picked when the link is clicked. For example, if you set Outlook as your mail client, Outlook will open a new email if you click on a mailto link.
You can also use icons instead of the entire link in your tables by using the URL icon option in the Values card of the formatting pane.
If you are using the Power BI service, the browser will handle opening the link. In the Desktop, we will prompt you with a message confirming you want to continuing launching another app.
If you want to use file URLs, there are some limitations. They will not open in the Power BI Desktop, only in web browsers. For most browsers, you will need to right click on the link, copy the link address, and paste the URL in a new tab.
Find more details about URLs in tables and matrices in the following video:
Mailto links in textboxes
We’ve also added support for mailto links in textboxes. This is great if you want to give your report viewers a person or group they can contact if they have any questions.
Find more details about mailto links in textboxes in the following video:
Analytics
Report level measures for live connections to Analysis Services tabular models & Power BI service da...
If you are creating a live connection to a tabular Analysis Services server or a Power BI service dataset, you can now create new measures using DAX. These measures will not be part of the model. However, they will behave just like measures defined in imported models, meaning you can set their data type and formatting, see error messages when you break the measure expression, and use intellisense when editing the expressions. Also, they can be used in reports published to the Power BI service and used in visuals pinned to dashboards. You create them in just the same way as you would for an imported model, from the New Measure button in the ribbon, or by right clicking in the field list. We currently only support report measures a live connection against Analysis Services Tabular models. You won’t be able to create measures when connected to Multidimensional cubes, but we’ll consider this for a future release based on your feedback.
Find more details about report level measures in the following video:
2 new quick measures (preview): Total for category & Rolling average
We’re building on last month’s release of quick measures with two new additional calculations: Total for category and Rolling average.
Total for category will calculate a total across all the values in a category. There are two options for Total for category, one that will respect filters applied in the report and one that will ignore the filters. Both can be found under the Totals section.
These are useful building blocks for creating measures that calculate the contribution to a total amount or percentage of parent.
Rolling average will perform an average of the measure chosen across the given number of periods. It can be found under the Time Intelligence section.
We’ve also added made some general improvements to the quick measures feature. First, for some quick measures you can now pick if blanks should be treated as zeros or be blank in the calculation.
This is important when visuals use an attribute where the measure doesn’t have a value. For example, if your date dimension extends further than the data, you might want your quick measure to return blanks when the base value is also blank.
Second, quick measures will now match the formatting of the base measure if the chosen measure doesn’t need a specific formatting. For example, doing a year to date on a currency column will preserve the currency formatting from the base measure. This should save you some time when creating new measures.
Find more details about quick measures in the following video:
Bin by count
When creating bins, you can now decide how many bins you want, and we will determine the correct size for the bins. We’ve also made some improvements to the binning dialog in general. When creating bins, we show the min & max values of the source column and the bin size. We also now recommend a number, or size, of bins to help pick the best grouping. This is based on the Rice rule:
Where k is the suggested number of bins and n is the number of points (rows) in your data – we also make sure won’t have more bins than you have data points.
Find more details about binning in the following video:
Data Connectivity
New connector: Dynamics 365 for Customer Insights
We’ve added a new connector that allows you to import and analyze data from your Dynamics 365 Customer Insights service. The connector can be found under the Online Services category in the Get Data dialog.
Find more details about binning in the following video:
Combine files – ability to reference “First File” as example
This month we’re improving the Combine Files experience by allowing you to always reference the “first file” in a folder as the example file. Before this update, you had to pick a specific file by name, which might cause errors in the future if the file is removed from the folder.
With this month’s update, you can select “First File” in the Combine Files dialog, which will ensure that the first file in the folder is used as the example, regardless of the specific file name.
Find more details about the combine files improvement in the following video:
Query editing
Two new Transforms
We are adding two new data transformations to the Query editor this month.
Extract text before/after/between delimiters
A common request is being able to extract all text before, after or between delimiters from a Text column. Based on this feedback, these new options have been added to the Transform and Add Column tabs in the Query Editor ribbon, under the “Extract” dropdown menu.
Unpivot Only Selected Columns
A new transform has been added under the “Unpivot Columns” menu that allows you to unpivot only the currently selected columns in the Query Editor preview. This will generate an explicit columns list in the current step so that the same set of columns is unpivoted on future refresh operations.
In addition to the newly added “Unpivot Only Selected Columns” option, you also can apply one of the following options from the “Unpivot Columns” menu:
- Unpivot Columns: This operation will unpivot all columns except all the non-selected ones. This case is optimized for scenarios where new columns that appear in the future need to be unpivoted as well. For example, datasets where new columns represent data for new dates (i.e. monthly sales, weekly occurrences, etc.)
- Unpivot Other Columns: This operation provides the same capability as “Unpivot Columns” in terms of future behavior with respect to new columns appearing in the table. The main difference with “Unpivot Columns” is that it allows you to select that columns that should not be unpivoted. This case optimizes the user flow for cases where the number of columns that should not be unpivoted is much smaller than the number of columns that should be unpivoted, similarly to the behavior of “Remove Other Columns” compared to “Remove Columns”.
Find more details about these new transforms in the following video:
That’s all for this month! We hope that you enjoy this new update and continue sending us valuable feedback about our product. Please don’t forget to vote for other features that you would like to see in the Power BI Desktop in the future.
And don’t forget to register for the 2017 Microsoft Data Insights Summit! I can’t wait to see everyone there.