Power BI – A Smashing Update!!!
Hey everyone and welcome back!! Just when you thought that Power BI has done it all, they come out again with this month’s SMASHING Power BI Desktop update. While these updates won’t leave you green with anger like our friend THE HULK here, they will make you feel POWERFUL and SUPERHERO-ISH!!
So let’s dive in and see what they guys at Power BI has done for us:
A short summary of this month’s updates……
- Multi-select data points across multiple charts
- Sync slicers across multiple pages of your report
- Numeric range slicer improvement – whole number snapping (preview)
- Faster geocoding for Bing maps (web preview)
- Overflow data labels for bar and column charts
- Search the formatting and analytics pane
- Mark custom date table
- Quick measures are now generally available
- UTCNOW() and UTCTODAY()
- Organizational custom visuals (preview)
- Network Visualization and Filter
- Slope Chart by MAQ Software
- Filter by List
- as Timeline
- as T-Accounts
- Improvements to DirectQuery over SAP HANA (preview)
- DirectQuery performance improvements
- Open and save performance improvements
- “Show items with no data” improvements
- Persistent filters control for up coming Power BI service feature
- Consulting Services quick link
As you can see, this month’s Power BI updates include some muscle swelling, strength gaining, iron pumping properties…..
“Grrrrr whhh aaaahhh
Multi-select data points across multiple charts
These kinds of updates make me very happy, as they really improve on the interactivity of your reports and dashboard – improving the overall user experience. This month Power BI has given us the ability to click on data points on multiple charts and have cross-highlighting applied to your entire report – awesomeness!! Below I have a GIF which shows how this is done – all you do is use Ctrl + Click and choose the relevant data points you would like to filter on.
Sync slicers across multiple pages of your report
Have you ever been one of those people who have many monthly or quarterly reports to produce and for which you need to change the period / date or other slicers manually for every report in your Power BI File? A real pain right?! Well now, with this new update – you can apply one or more slicers once-off (called syncing slicers) and tell the system which pages to apply your slicers to – oooooh yeaaaah!!!
To start, let’s activate ‘Sync Slicers’ – go to the ‘View’ tab, under the ‘Show’ group, check the box next to the ‘Sync slicers’ description.
Once you’ve done this, a new pane called ‘SYNC SLICERS’ will appear next to your other panes. For now, it won’t show anything in it, until you select a slicer. To demonstrate this functionality, I’m going to create a ‘Landing Page’ where I can apply the relevant date range (Year & Month) for which I want my reports to show data and then tell the system on which of my reports I would like the slicer applied and visible. When you select the slicer as visible, it basically creates a duplicate slicer in the same place for the selected report. For my reports, I want the slicer to be visible only on the landing page. Let’s check it out…..
Numeric range slicer improvement – whole number snapping (preview)
You may have remembered this slicer from a while back – the numeric slicer. Previously, it had a little issue where it presented whole numbers as decimal numbers – this has now been fixed.
Faster geocoding for Bing maps (web preview)
Once thing you would have notice if you ever worked with a large number of addresses in Power BI, is that it takes a bit of time to load as the Bing engine have to geocode each address on the fly every time the map is refreshed or a filter is added. The guys at Power BI have now improved this load time by 6 – now that’s some real geocoding muscle!!!
They have rolled out this functionality only to a small group of users at the moment, and will continue to release to more and more in the days to come. If you don’t have this functionality yet, stay tuned – I’m sure it will reach everyone soon. Once thing to note, the feature will only affect maps in the Power BI Service, and won’t work on your desktop reports.
Overflow data labels for bar and column charts
This next addition and a follow up on the iterative updates on chart labels, is a very welcome one. Usually Power BI will hide all your data labels if they don’t fit and you placed them inside data points. Now you will be able to format your data labels so that they overflow, past the edge of the data point. Note that labels that will collide with others will still be automatically hidden to ensure that text don’t overlap. Let’s have a look at this feature….
Search the formatting and analytics pane
Power BI have spoiled us with so many options now in the formatting and analytics panes that it can sometimes be hard to find the option you are looking for. To help with this, they have added a search box that will filter down the options to only those that match your search terms.
Mark custom date table
Date tables, we couldn’t live without them in this Power BI world. They are a fundamental part of our Power BI models and give us the ability to do some pretty neat time intelligent calculations. Until now, you could only bring your own date tables by importing from Excel (using PowerQuery) or through LiveConnect (SSDT). Of course there has also been the ability to generate these date tables with functions such as CALENDAR() and CALENDARAUTO(). With this new Power BI update, you can mark any table as a date table which will enable the date hierarchies and time intelligence for the selected table.
In order for a table to be marked as a date table, it must contain at least one column of type ‘Date’, or ‘Date/Time’. You can mark your own table as a date table either through right-clicking on the desired table or using the ribbon button.
When marking a table as a date table, you must select a date column to be used as a primary date column. Make sure that the column you pick for this:
- Contains no null values
- Contains unique values
- Contains contiguous date values (from start to end)
- If it is a date/time column, the timestamp must be the same across each value- i.e. 12:00:00 AM
Quick measures are now generally available
Our quick measures feature is now generally available and no longer requires the preview feature switch to be turned on. Additionally, thanks to the above feature to mark your own custom data table, you no longer need to use Power BI’s date hierarchy for the time intelligence quick measures. You can use the date field from your own date table in any quick measures.
UTCNOW() and UTCTODAY()
Power BI has now added two new DAX functions this month that help if you’re working with date-time data across timezones – very handy indeed. With the NOW() and TODAY() functions that we’ve used until now, passing a Power BI file to a user from a different timezone, leaves you with an issue that the time that you have worked will show incorrectly when they open the file. UTCNOW() and UTCTODAY() will always return the current time or date in UTC so you can guarantee consistent results wherever you are – even when you upload your workbook to the Power BI service. Try it out!!
While we do not look at the custom visuals that were released as part of this months Power BI desktop update, be sure to check out these awesome custom visuals from the Power BI blog page.
- Organizational custom visuals (preview): Your organization’s Power BI admin can deploy specific visuals as part of your organizational repository. This means you’ll see these deployed visuals under a new section of the Power BI custom visual store integrated into Power BI Desktop called My organization. Since these visuals have been vetted by your company, you can have confidence that they are well made.
- Network Visualization and Filter: The Network Chart by ZoomCharts has the ability to visualize data as a network structure and filter data through a variety of gestures and smart touches on all devices. The network structure lets you have multiple vertice types (more than two), making it possible to see the connections among many categories.
- Slope Chart by MAQ Software: Slope Chart by MAQ Software allows you to analyze trends in data at a glance. This visual is useful for comparing interactions between two data points based on time or other user-chosen parameters. A slope chart is most useful if there are notable changes to highlight or for showing comparisons between groups.
- Filter by List: The Filter by List visual lets you bulk apply filter values for a report. Currently if you want to filter a report with a large number of categories, such as 100 products, you’d need to check each box in the filter pane one by one. With this visual you can do this immediately by pasting the entire list of products into the slicer visual.
- as Timeline: This visual helps you find overlaps and gaps of dates in your data. This is useful for showing things such as team vacation schedules or project plans.
- as T-Accounts: T-accounts are used by most accounting professionals to understand difficult accounting journal entries and with this visual you can now create T-accounts in Power BI directly.
These are some pretty cool visuals right?
Improvements to DirectQuery over SAP HANA (preview)
Power BI has for a long time been supporting SAP HANA connections, both for import and DirectQuery connections. However, DirectQuery has largely treated SAP HANA as a relational source, which means that care had to be taken to ensure that measures aggregated correctly when dealing with non-additive SAP HANA measures like ratios or counters and to ensure good reports performance.
Power BI now has a preview of a new approach to treat SAP HANA it as a multi-dimensional source by default. This will work in a similar manner to SAP Business Warehouse or Analysis Services. You’ll need to turn on the “New SAP HANA connector” preview feature switch under File > Options and Settings > Options > Preview features. Follow the steps below to get this done.
When connecting to SAP HANA using the new approach, a single analytic view is selected, and all the hierarchies, measures, and attributes of that view will be available in the field list. This will ensure that as new visuals are created, that Power BI will aggregate the data correctly.
You can also still use the the old approach to DirectQuery over SAP HANA. This can be done by going to File > Options and settings and then Options > DirectQuery, then selecting the option Treat SAP HANA as a relational source. Check this option if you want to create a DirectQuery report over SAP HANA using the old approach.
Note that this option controls the approach that will be used for any new connections created in Power BI. It has no effect on any SAP HANA connections already existing in the current report, nor does it have an effect on connections in any other reports that have been opened. This means that any reports connecting to SAP HANA that were created prior to February 2018 will continue to treat SAP HANA as a relational source.
Given the completely different behavior between the two approaches, it’s not possible to switch an existing report from one approach to the other.
The two approaches are described in detail in the Power BI documentation.
Unfortunately, while using this connector in preview, reports created using the new approach can’t be published to the Power BI service as this will result in errors when opened within the Power BI service.
There are some pretty awesome improvements this month with regards to general performance of the Power BI desktop application. While I won’t go into too much detail on these, you can read more on these improvements over Here. This latest update includes a large number of improvements to the performance of reports using DirectQuery. We expect to see some awesome improvements in queries in general.
DirectQuery performance improvements
Combining additive and non-additive aggregates into single query.
Improved handling of queries with a large number of literals.
Use of a single SQL query rather than multiple for some visuals and calculations.
Optimized queries to reduce post-aggregation.
Optimized use of TREATAS in calculations.
Fewer SQL queries are sent when there is multi-column tuple filtering.
Open and save performance improvements
Power BI has made improvements to the performance of your desktop app when opening and saving files. Especially when files are quite large. They say we can expect to see a performance improvement of between a 40% and 60% – now that’s impressive!!
“Show items with no data” improvements
For your visuals that have the ‘Show items with no data’ enabled – we will now see major performance improvements for visuals with columns or filters directly related in the model – as in one to many relationship models. This seems to be greatly improved, so I’m looking forward to testing this out!!
Persistent filters control for up coming Power BI service feature
Power BI is also planning to release a new feature for reports in the Power BI service. This new feature will have the ability to persist filters, slicers, and other data view changes as your users last left them – oh yeah!! A huge time saver if you use a lot of filters!!!
So they have given us more control over this feature when it does get released. So for now they have added a new option under the Report settings called “Persistent Filters”. by default it is switched on, but you can also choose to disable it there.
Consulting Services quick link
Power BI now has under the ‘Help’ ribbon a new ‘Consulting Services’. While these packages look great – feel free to just contact your friendly Databear to give you a world class service, training or solution development, backed by years of experience and a passion for excellence ;-)
And that my good friends is a huge update for the month of February (almost bigger than Hulk’s biceps).
And that’s it for this update folks – I trust that this SMASHING update will bring you one step closer to being a super powerful analytical beast……saving the business world, one report and one day at a time…
Until next time, keep safe, and stay blessed!!