Power BI RoundUp – (Desktop Update Oct 2018 – Aggregations for Petabytes Scale – Webinar Ask Anything on Power BI Premium – Power BI Report Server Timing)

I am sure that there were a few people who were eagerly waiting for the Power BI Desktop Update for October 2018. I sure was one of them and it was well worth the wait. Please read all the details below, as well as other updates that happened in the past week.

Power BI – Desktop Update Oct 2018

There has been yet another massive update to the Power BI Desktop release for October 2018, and as I do every month I will highlight what I feel is relevant in my opnion.

Reporting

There is now the ability to be able to search within a filter for items. This is especially useful when there are a lot of items in your filter.

Every month there are more accessibility features that are being built into Power BI. This to me shows that Microsoft and the Power BI team are serious about not only helping people with accessibility challenges to view reports, but also allow these same people to create incredible Power BI reports.

As I showed in the initial image there has been a big performance improvement for ArcGIS maps. They respond now almost instantly and zoom into the area that you selected, which makes the reports that much faster. And who does not like near instant response times?

Modelling

Another BIG improvement has been to the DAX editor. Now there are line numbers, ident lines, as well as showing where you are in your code, and if there is an error the bar will highlight it in red.

Along with this there are a whole host of Shortcut keys (which I still must learn and memorize) which makes the editing of DAX measures that much easier and quicker.

Shortcut Key Combination What it does
Alt+ ↑ / ↓ Move line up/down
Shift+Alt + ↓ / ↑ Copy line up/down
Ctrl+Enter Insert line below
Ctrl+Shift+Enter Insert line above
Ctrl+Shift+\ Jump to matching bracket
Ctrl+] / [ Indent/outdent line
Alt+Click Insert cursor
Ctrl+I Select current line
Ctrl+Shift+L Select all occurrences of current selection
Ctrl+F2 Select all occurrences of current word

Analytics

Even though I do not have a picture, I wish I did, because having the Aggregations and Composite Models in the Power BI Service is honestly a BIG BIG Deal. I have been looking into this and it is a total game changer for when you do not need or want to load ALL the data into a data model for those exception reporting cases (When one or very few users require more granular data).

Not only that but it makes the performance of the Power BI reports so much faster when visualising the data over a large dataset. I cannot wait to implement this.

They are building more smarts into Power BI Desktop, now where it can explain the increase for non-additive measures. This could provide some insights that before would have taken quite a while to figure out yourself. Not only that but if the visual is what you want to show you can then add this to your report.

Custom Visuals

I am not going to cover all the custom visuals, suffice to say that in the blog post there are a lot of additional custom visuals.

Data Connectivity

There are a whole host of Data Connectors that are now generally available below:

  • Web by Example
  • SAP BW Connector V2
  • SAP BW Message Connector
  • Vertical Connector
  • Dynamics NAV
  • Dynamics 365 Business Central

And there is a new connector for Dynamics 365 Business Central On-Premise Connector

Data Preparation

Wow, this is what I would consider a 3rd massive update to Power BI Desktop in one month, where you can now profile your data in the Power Query Editor.

This is something that I know a lot of people have been asking for, and it does provide a quick way to understand what your data looks like, how many distinct values etc. It also ensures that your data is as you expect. Not only that but you can also easily shape your data based on the data profiling within the charts that are displayed.

There is now an option to use fuzzy matching to compare in the merge, where you can set some additional options:

  • Set the threshold of how high or low you want to try and match
  • Ignoring case or spaces
  • Maximum number of matches to match on.
  • Transformation table, this is where you can have a table where it might not appear to be related, but in the transformation table it could have those translations between what potentially the business calls an item and what it is coming from the data source.

One thing to note for the fuzzy matching you will need to install the October version of the On-Premise Data Gateway for it to refresh in the Power BI Service.

Other

There are now additional export data options where you can set how the data can be exported or even not at all.

Once thing which Amanda did call out in her video is that the Tenant Settings will always be enforced. This means if the tenant setting says that no data can be exported no matter what you set in the above image, it will not give you the option in the Power BI Service.

If your organization has changed from TLS 1.0 to TLS 2.0 and the above registry keys have been updated Power BI Desktop will respect those settings.

Here is this months MASSIVE blog post: Power BI Desktop October 2018 Feature Summary

Power BI – Aggregations for Petabyte Scale

This is a great blog post by Christian Wade from the Power BI team where he discusses the high-level overview of how Aggregations can enable your organization to leverage their petabyte scale data for blazing fast reporting performance.

You can find the blog post details here: Aggregations for petabyte-scale BI available in the Power BI service

Power BI – Webinar Ask Anything on Power BI Premium

There will be a webinar on 18 Oct 2018 PST, where Chuck will have Josh Caplan from the Microsoft Power BI team where you will be able to ask him anything relating to Power BI Premium.

With the recent updates to Power BI Premium and the usage reporting that is now available I am certain that there will be a lot of interesting questions, as well as insights into Power BI Premium. This is one Webinar that I want to attend, and if I cannot attend I will watch it later.

Here are more details: Webinar 10/18 Group Program Manager, Josh Caplan, and the Power BI team host an Ask Anything around managing and monitoring Power BI Premium

Power BI – Power BI Report Server Timing

It is great to see that the Power BI Report Server team have now announced a dedicated release schedule for Power BI Report Server.

This will now be Jan, May and Sept each year.

I know that typically Power BI Report Server is installed in organizations who have a standard way of working and upgrading or updating systems. By having a dedicated release schedule these can be planned for accordingly.

All the details are here: Power BI Report Server release timing update

Power BI Gateway – How to remove a Gateway Instance from the Gateway Cluster

I recently was troubleshooting a performance issue for a customer where their LiveConnection was slow when connecting to an On-Premise SSAS Tabular server via the On-Premise Data Gateway.

One of the things that I had to do was to remove an existing On-Premise Data Gateway from a cluster, so that I can then move the Gateway onto another server.

NOTE: Even if you uninstall the On-Premise Data Gateway from a server it will still exist in the Cluster Configuration, so I had to ensure that I had it completely removed.

Whilst I was going through the steps I found that it was a bit of a challenge to complete the steps, so this inspired me to complete this blog post below. I do hope that in the future this could be part of the On-Premise Data Gateway.

I completed all the steps below using PowerShell, and whilst PowerShell might appear to be difficult, if you follow the steps below it should be straight forward (Hopefully my blog instructions are easy enough to follow!)

Running PowerShell Scripts

The first thing that I did was to run the PowerShell scripts where I have already installed the On-Premise Data Gateway. This is because the PowerShell scripts are part of the installation.

I opened PowerShell ISE and run it as Administrator

Importing the module

In PowerShell I then use the code below to navigate to the Data Gateway’s Folder

cd 'C:\Program Files\On-premises data gateway'

I then imported the module as shown below.

Import-Module .\OnPremisesDataGatewayHAMgmt.psm1

Logging into the Power BI Service

The next step I had to complete is to log into the Power BI Service, because this then enabled me to get the details which are part of my tenant.

Login-OnPremisesDataGateway -EmailAddress gilbert@mydomain.com

Getting all the Gateway Clusters

Next, I wanted to get a list of all the Gateway Clusters which are within my tenant.

NOTE: In a large organization there might be a lot of Gateway Clusters, so it might be better to export the data into a file if this is the case.

I then ran the following PowerShell script below to show me all the Gateway Clusters.

Get-OnPremisesDataGatewayClusters

And this was the output

Getting the Member Gateways inside a Specific Cluster

What I now recommend doing is to first find all the members of a specific Gateway cluster.

This ensures that not only do I have the current Gateway Cluster ID, but also ensures that I am only getting the members of the gateway where I want to remove a member.

As with my example above I put in the following into PowerShell

Get-OnPremisesDataClusterGateways -ClusterObjectId 9c9697b7-XXXX-XXXX-XXXX-b481de215945

The output was a list of the members that were part of my gateway as shown below.

I could then see above that I had 2 members in my Gateway Cluster.

The one thing that I did note is that the Primary Instance in the Gateway Cluster has the isAchorGateway = True

I wanted to remove the second member in the Gateway, so I made a note of the gatewayObjectId

Removing Member from Gateway Cluster

I then ran the following code in PowerShell below to remove the member from the Gateway Cluster

Remove-OnPremisesDataGateway -ClusterObjectId 9c9697b7-XXXX-XXXX-XXXX-b481de215945 -GatewayObjectID 3ed46c5d-XXXX-XXXX-XXXX-eb13b6c676d

Once the above was completed it came back to the PowerShell prompt.

Confirmation that I had removed the member from the Gateway Cluster

The final step I did was to confirm that I had removed the member from the Gateway Cluster.

I re-ran the PowerShell script which shows the members of the Gateway Cluster

Get-OnPremisesDataClusterGateways -ClusterObjectId 9c9697b7-XXXX-XXXX-XXXX-b481de215945

And now I got the output where only the Primary member in the instance remained

Conclusion

In the steps above I have shown how to remove a member from an instance of an On-Premise Data Gateway Cluster.

What I did then do with my customer was to move the Gateway Cluster over to a new dedicated Virtual machine, and I did this by installing the On-Premise Data Gateway software on a new server, and then migrated the On-Premise Data Gateway to the new dedicated virtual machine.

I always enjoy getting comments and suggestions from my blog posts, so please feel free to comment to suggest something.

Power BI RoundUp – (Power BI Preview Features in Action – Developer Update for Sept – Webinar on Power Query & M for Calendar Tables)

Welcome to another week of the weekly Power BI RoundUp, I hope you enjoy catching up on the latest content below.

Power BI – Power BI Preview Features in Action

In this blog post by Chuck (Charles) Sterling he goes into details on all the preview features that are going to be shown at the last 3 stops of the Power BI World Tour.

I went to the Sydney event and I thoroughly enjoyed it, I met some new friends, networked and learned a lot.

All the details on what will be shown is available here: See Power BI Preview Features in action at the Power BI World Tour

Power BI – Developer Update for September

As you can see above there were 3 updates this month.

The first being an update to the Capacity Metrics App for Power BI Premium workspaces where you can now not only monitor capacity, refreshes and data loads, but now there is also query metrics which is great to see if there is a long running query or a query that consumes a lot of memory and CPU.

There are now also advanced URL filtering capabilities, which often can be used to either compliment or to extend access to data for specific users or groups of people.

There is a new API where you can update the connection between cloud and on-premise data sources via the API. This is really a great improvement, as often data sources move around and with this API it makes it a lot easier to get this done programmatically.

And finally, Row Level Security now supports ASCII characters in the Username property.

All the details can be found here: Power BI Developer community September update

Power BI – Webinar on using Power Query to create Calendar tables

My friend from France will be presenting a Webinar with Chuck from the Power BI team on using Power Query to create calendar tables.

I have no doubt that this will be a great webinar and I find that there is always something to learn when watching other people, us M.

The Webinar details can be found here: 10/11 Webinar: How to use Power Query and M to create calendar tables with Tristan Malherbe

Power BI – Modifying the Colours in the Options of the Visual Headers

I was recently consulting at a customer and I was looking to better understand if there was a way to disable the exporting of Underlying data, but whilst investigating this I came across the formatting pane and as shown below I could modify the menu in the Options for Visual Headers which I detail below.

The first step is to make sure that you have enabled the “Use the modern visual header with updated styling options” in the settings in Power BI Desktop as shown below.

Next,I clicked on the Visual and went into the Format options for the Visual and searched for Visual Header

This is what it looks like after I have configured the Visual Header settings

And this is how I configured it.

The first thing that I did was to change the Background Colour

I then made the Border Colour the same as the Background Colour, so that it looks like a single colour.

I changed the transparency to 0%, this is so that it will display correctly over other visuals as shown below.

This is what it looks like with the above transparency.

But if I had to change the transparency to 50% this is what it would look like below, which might be confusing to the users interacting with the report and visuals.

The final thing that I did was to update the font colour to better match my visuals.

And once again, this is what it looks like once complete.

One additional thing to note is that I made the Title have the same look and feel by using the same background colour and font for the Title.

If there are any comments or suggestions, they are always most welcome in the section below.

Power BI RoundUp – (Premium Capacity Metrics App – Power BI Home & Global Search)

I have decided to rebrand or change the name of my weekly round up from BI RoundUp to Power BI RoundUp, because it is almost always about Power BI. And with the new direction from Microsoft this makes perfect sense.

As always please find the latest updates below.

Power BI – Premium Capacity Metrics App

This new app is great for people who are using Power BI premium and want to gain a better understanding of how the premium capacity is performing.

It gives you insights into Refreshes of your data, Queries (which is great to see long running queries which could consume resources) and could optionally be optimized) and datasets.

If you are using Power BI Premium this is something I would suggest installing.

Here is the blog post details: Premium Capacity Metrics app – query metrics now available

Power BI Home & Global Search

Power BI Home was seen at the Microsoft Business Applications Summit earlier this year and it is great to finally see it here. I personally think that this is a great place for people to land when logging into Power BI. It is a great start and I know that there are going to be future enchantments to this.

One of the great features is the global search which will allow you to search and find all the related content that you have access to. I find that as more reports, datasets and dashboards are deployed it can become a bit of a challenge to find the report you are looking for. Now with global search this is a lot easier.

More details and more in-depth details can be found here: Introducing Power BI Home & Global Search

That is it for this week, enjoy the rest of your week.

Power BI InfoGraphic Update – Sep 2018

It has been a few months since I have updated my Power BI InfoGraphic and with the speed at which this all changes and updates I have always had a challenge to keep it up to date.

The following items have been added with links to more details:

Please find below the latest version as at September 2018. I always also keep the latest version of the InfoGraphic here: Power BI InfoGraphic Overview

You are welcome to share this infographic with anyone, as long as the author is given credit for the creation.

If there is anything missing or needs to be updated, please leave it in the comments section below.

BI-RoundUp – Power BI (Service & Mobile Update for July & August 2018 – User Research Panel – On-Premise Gateway Update for Sep – Webinar on Power BI Dashboard Comments)

Here is this weekly BI-RoundUp where I will detail what updates there have been in the past week.

Power BI – Service & Mobile Update for July & August 2018

In the past two months there has been a lot of updates to the Power BI Service and the Power BI Mobile App. All the details are below with links as per the blog post.

I am not going to go into any detail, due to it already been covered before, or it is the relevant links below.

DAX – Getting the Start Date of the following Week

I was recently doing some work for a customer where they wanted to compare their date to the start date of the following week. They also had a requirement to persist the data to the dataset.

I thought that this would be a good challenge to use DAX with a calculated column. I most certainly could do this using the Power Query Editor and M, but I always like to see if it is possible when it is a smaller and easier DAX calculation.

Below is the DAX to get the Start date for the following week

'Date'[Date] - WEEKDAY('Date'[Date],2)+8

As you can see below, as each week goes on, I have got the start date for the following week for each day of the current week.

If I wanted to change it and use the starting date of the current week and use it for all dates for the current week, I could change it with the following DAX calculation

'Date'[Date] - WEEKDAY('Date'[Date],2)+1

As always if there are any comments or suggestions please leave them in the comments section below.

BI-RoundUp – Power BI (Sep Update for Power BI Desktop – Dashboard Comments – Community Contribution to PowerShell Cmdlets – Power BI PowerShell Cmdlets available in Azure Cloud Shell)

Welcome to another week of my Power BI Round Up. And as expected there is another massive Power BI Desktop Update.

Power BI – September Update for Power BI Desktop

As always I am going to over the new features and highlight what I think is really relevant in terms of what has been released.

Reporting

With the added support for categorical fields on the X-Axis this now makes the scatter charts more flexible.


As shown above there now is the capability to be able to copy a single value, which will copy the value in its unformatted form, which could be used to put into a search of another application.

Whilst when using the Copy selection feature, it will copy everything that you have highlighted in your table or matrix. And when pasting this, it will keep the column headers as well as the formatting options which you can then paste into Excel or another program. This is really a great piece of functionality which makes it easier when certain data needs to be copied into another system.

There are now some built in report theme’s which are great for those people who just want to move from the default colours, but do not want to create their own custom themes.

Report Page Tooltips are now generally available meaning that you will not have to enable it as a preview feature.

Along with that as shown above there is now report page tooltips available for Cards.

There have also been additional improvements for accessibility in the formatting pane, making it easier to navigate, as well as when using the screen reader there is more support.

Analytics

This is a really big, BIG one having the ability to have aggregated tables in your data model. What this means is that you can have a highly aggregated table of your data which might answer 60 – 70% of the queries. When the queries are run they will use the data in the aggregated table, which in turn results in exceptionally super-fast query response times. And if it does not hit the aggregated table it will then go down to the source table, which could be imported or using Direct Query mode, which means then that the entire data model is actually very small but very fast.

There is a lot more to this, so I suggest reading up the documentation that is included in the blog post. But this is something that I have been waiting for and cannot wait to try it out and get it working.

Finally there is now support for RLS with Q&A, this means if you have had a dataset that has had Row Level Security (RLS) previously you could not use Q&A. This has now been fixed and Q&A is available which is awesome.

Data Connectivity

As shown above there is a new connector where you can connect to PDF files and it will attempt to extract table data from your PDF files.

There is now support for the SAP BW Connector measure properties

There is a new connector called dataflows which will soon be in limited preview which will allow users to connect to an existing data flow.

Data Preparation

As per my first image, and above, there is now Intellisense when going into the Advanced Editor in the Power Query Editor. This is so awesome. There have been so many times in the past when I have not known which M function to use. As well as having syntax errors, and a lot of that pain is now gone with having Intellisense.

The add columns from examples now supports text padding your data, as shown above you can pad the text with zeros for data where you want them all to have the same length.

It is interesting to see that right at the end of the blog post they highlighted that they are working on being able to copy visuals between PBIX files. How cool is that??

You can find all the blog post details here: Power BI Desktop September 2018 Feature Summary

Power BI – Dashboard Comments

This was announced at the Microsoft Business Applications Summit and it is great to see that you now can add comments to Power BI Dashboards, as well as individual tiles on the dashboard.

It is also great that you can tag people in the comments, which will then send an email or send a push notification to the Power BI Android and IOS Mobile App.

At the end of the blog post, they also indicated that this will be coming to the reports, so stay tuned.

You can find all the details here: Announcing Dashboard Comments in Power BI: Your new hub to discuss data and collaborate with others

Power BI – Community Contribution to the PowerShell Cmdlets

This is the first contribution from the Power BI Community which adds to the existing Power BI PowerShell management cmdlets. Where Kenichiro Nakamura has added functionality to work with datasets, tables and columns.

You can find a lot more detail in the blog post: Celebrating our first community contribution to the Power BI management cmdlets

Power BI – PowerShell Cmdlets available in Azure Cloud Shell

What this means is you can now use the Azure Cloud Shell to run the Power BI PowerShell Cmdlets. This also means that there is no need to try install and configure the required PowerShell modules, because this is all maintained in the Azure Cloud Shell.

I would suggest looking into the storage options because depending on how much storage you need, if you have an existing Azure Subscription this could be a very good option.

You can find more details on how the Azure Cloud Shell works, as well as the blog post details here: Power BI Management cmdlets in Azure Cloud Shell

Power BI Default Slicer Selection

I have recently had some discussions with regards on how to enable the default selection of a slicer which will always keep the slicer selection on the current month.

As is currently stands there is no out of the box way to change the slicer on a selection where it shows the actual date for each day. This is because currently you can only have the selection on a value that does not change at every data refresh.

My proposed solution below is to use a fixed value, which does not change each time the data is refreshed.

I always suggest that it is best practise to complete the fixed value in the Power Query Editor. The reason is twofold.

  • Firstly, it is a lot easier to use the Power Query M functions to get the output you require.
  • Second, it allows for better compression into the data model.

As always, I will use the working example below, where I want my slicer to always select the current month.

  • I already have got a date table created, which is another recommendation to have in every data model, and you can refer to my blog post Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query which explains how to create the date table.
  • Next, I create the column with Year-Month with the code below, and the reason for this is because there are 12 months that happen over every year, without having the Year and Month combination it will result in multiple “Current Months”
    Number.ToText( [Calendar Year]) & "-" & Number.ToText( [Calendar Month Number])

     

    • And this is what the column looked like.
  • My next column that I created was to get the current Year-Month combination, which I created with the following code below.
    • NOTE: The code below does appear to be quite long, but based on my blog post, I have parameters for the #”Time Offset in Hours”, this is so that I when the data is being refreshed in the Power BI Service, it will then update correctly and show the correct Year-Month combination.
      Number.ToText(Date.Year(DateTimeZone.FixedLocalNow()+ #duration(0,Number.From(#"Time Offset in Hours"),0,0))) & "-" & Number.ToText(Date.Month(Date.From(DateTimeZone.FixedLocalNow()+ #duration(0,Number.From(#"Time Offset in Hours"),0,0))))

       

    • And this is what the column looked like.
  • The final step in my process is to then create a conditional column to compare the two columns above and where they match it will then be the current month, and when it is not, it is another month.
    • Which results in a column which will then give me a fixed value which says “Current Month”
    • And this then allows me to use this in a slicer, so that every time the data is refreshed the fixed value of “Current Month” does not change, but it will move as time moves.
    • As an option I then removed the previous 2 columns that I used because they are no longer needed.

As always, I am sure that there is more than one way to do that, but for me completing it in each step allows me to view and see that it is working as expected. And then to ensure that I am getting the results I expect.

Comments or suggestions are welcome in the section below.