BI-NSIGHT – Power BI (Tachometer Custom Visual, Azure Active Directory Content Pack, ZynBit using Power BI Embedded) – SQL Server (CTP 1.2 Available)

There has been some great activity this past week in the world of Business Intelligence and here are the updates.

Power BI – Tachometer Custom Visual

As you can see from the image above, there is a new Custom Visual that has been added called the Tachometer.

This is a great visual when you want to use it to easily communicate measures against various attributes. As you can see above there are a whole host of details to configure.

You can find the blog post here: Visual Awesomeness Unlocked: Tachometer Gauge Custom Visual

Power BI – Azure Active Directory Content Pack

This is a great content pack from Microsoft so that you can see very quickly how your Azure Active Directory is being used.

This can give some great insights into how Azure Active Directory is being used within your Organization.

One thing to note is that you have to be either a Security Reader or Admin within Azure in order to gain access.

You can find the blog details here: Azure Active Directory meets Power BI

Power BI – ZynBit using Power BI Embedded

This is a great customer story as in to how ZynBit is using Power BI Embedded to enable their business.

You can find the details here: ZynBit Empowers Sales with Microsoft Power BI Embedded

SQL Server – SQL Server CTP 1.2 Available

There is already another version for SQL Server Next Version on CTP 1.2

This has got support for SUSE Linux Enterprise, as well as the Early Adoption Program (EAP)

You can find the blog details here: SQL Server next version CTP 1.2 now available

Power BI – How to Filter Slicers dynamically

I had a requirement where I had a related table (or dimension table) in which was used as a slicer. But when it was changed to a slicer it was showing all the values in the related
table, even if there was no data.

So below I show and explain how to overcome this and show how to filter a slicer dynamically.

Example of current slicer

Below I will show what the default slicer looks like when you create it in your Power BI report.

As you can see above on the left hand side is my table, in which I have the Value and then the Fiscal Year.

And currently my Fiscal Year Slicer has Fiscal Years from 2011 – 2031.

And in my dataset we have data
until
2024

How to create a slicer with a calculated table

Below I am going to show how to create a calculated table, which will only have the values where there is data.

  • The first thing you have to ensure before you create your calculated table is that you have created your relationships between your tables.
    • NOTE: This is very important for the calculated table to work below.
    • As with my example you can see below that there is a relationship between the Budget data without new Year and the Date table.
  • In your Power BI Desktop click on the Modeling tab and then select New Table
  • I then put in the following DAX Syntax below, with the explanation afterwards of how it works.

    Fiscal Year – Slicer =

    CALCULATETABLE (

    ADDCOLUMNS (

    SUMMARIZE ( ‘Budget Data without New Year’, ‘Date'[Fiscal Year] ,‘Date'[Calendar Date]),

    “Not Used”, 1

    )

    )

    • As you can see above I started with my CALCULATETABLE, which creates the calculated table.
    • Next is where I have the ADDCOLUMNS, which will be which columns I want to add.
    • And then finally the SUMMARIZE, and this is where the DAX pattern comes to life.
      • The first selection in the SUMMARIZE is the table as shown with the Intellisense from Power BI Desktop.
        • So with my example I put the table as ‘Budget Data without New Year’ highlighted in Blue.
      • Now where it asks for the GroupBy_ColumnName1 this is where I actually selected the column name from my Date table.
        • And with my example I selected the ‘Date'[Fiscal Year] column highlighted in Green.
        • This is because the Slicer I wanted to use was the Fiscal Year.
      • Then the final column was the ‘Date'[Calendar Date] this is so that later I can create the relationship between the two tables.
        • NOTE: You can add additional columns if required.
      • And then finally because I used the SUMMARIZE I need to create an Expression as part of the requirement.
        • So for that I created the “Not Used”, 1 highlighted in Orange.
        • And what I did here was to give it a column
          name of Not Used and a Value of 1, so this satisfies the SUMMARIZE syntax.
      • What the above is actually doing from my understanding is that it is using the existing relationship between the Budget data without new Year and the Date
        table and only returning the Fiscal Years where there is data.
  • I then pressed Enter to create the table.
    • What I then did was to Hide the Not Used Column, so that this did not confuse the people using the report.
  • So as a quick test I then put in my Fiscal Year and changed
    it to a Slicer and I expected to only see Fiscal Years
    until
    2024 because that is when I have data.
  • A final thing to note is that by using the Calculated
    Table it is compressed, fast as well as always being updated on each refresh.

Creating a relationship from my Calculated table to my data (Fact Table)

The final step I did was to create a relationship from my Calculated table to my Data or Fact Table (Budget data without new)

  • The reason for doing this is so that when select an item on the Slicer it will filter
    due to the relationships being in place.
  • So I went into the Relationships view and created the following relationship as shown below.
    • As you can see above I created the Relationship between the Budget data without new and my new calculated table Fiscal Year – Slicer and on the Calendar Date columns.
    • NOTE: As you can see from the image above it does appear that the Fiscal Year – Slicer table does not have any data, it does and I think that this is possibly a small bug when creating a calculated table.

So now when I have my report and I have my Fiscal Year slicer I only see the Fiscal Years where I have data.

And if I click on a particular
Fiscal Year it filters as expected.

Conclusion

So in conclusion this is a great way to create slicers in which it will only have a slicer value where you actually have data. And this works really well when you have data that has a lot of historical values that are not used in the current context.

As with past examples you can download the Power BI Desktop File here: UnPivot Other Columns.pbix

BI-NSIGHT – Power BI (Email Subscriptions, New APIs for Custom Visuals, Accounts Receivable Solution Template for SAP, Help Improve Direct Query) – SQLBI (Vertipaq Analyzer 1.7) – SQL Server (Power BI Reports in Technical Preview, Getting Started with Technical Preview of Power BI Reports)

It appears that we are now getting back into the swing of things, and this means more updates and additions to the Business Intelligence World.

Power BI – Email Subscriptions

This was a feature that I know I have personally been waiting for as well as a lot of other people.

And yes this is very basic at the moment but you have to start from somewhere. And with this initial public preview you have the option to subscribe to a report page, which will be emailed to you Once a day when there is a change.

The great thing is that they are already letting me know that in a future release I will be able to subscribe other people also. And I have no doubt as the year progresses there will be additional updates.

You can read the blog post details here: Introducing email subscriptions in Power BI: stay informed when it matters

Power BI – New APIs for Custom Visuals

As you can see above there are 2 new Custom Visuals in the Gallery.

For me personally this is great, whilst I do not develop custom visuals, what this update does mean that there will be even more amazing and imaginative custom visuals in the near future.

I would love to see what other people have actually developed internally for their own datasets, so if anyone is willing even to share a picture of the Custom Visual please let me know.

For those who do develop Custom Visuals here is the blog: New APIs available for custom visuals developers

Power BI – Accounts Receivable Solution Template for SAP

As with the other solution templates, the people from Simplement have created a solution template for Accounts Receivable for SAP.

You can find the details here: Announcing the Accounts Receivable Solution Template for SAP

Power BI – Help Improve Direct Query

Below is a survey from the Microsoft Power BI Team with regards to people who use Direct Query.

Survey Link: Direct Query Survey

Blog Link: Help Improve DirectQuery!

SQLBI – Vertipaq Analyzer 1.7

I have to say that the new additions in the Vertipaq analyser are really awesome.

I like that you now have the ability to analyze your Power BI Files and see what is taking up all the space.

As well as the capability to extract all your DAX Measures. Which when working on a larger project and having to document all the measures this can be quite a cumbersome task. And along with this you can easily format the DAX expressions you the DAX formatter which I think is worth it.

You can watch the video with a great explanation of the updates here: VertiPaq Analyzer 1.7

SQL Server – Power BI Reports in Technical Preview

I know that for On-Premise customers this is something that they have been waiting for. Now you can download and install the technical preview with a lightweight installer that will take minutes to install and get you going.

What really is interesting is that you only need SQL Server 2008 or later database engine. And then SQL Server Analysis Services 2012 SP1 CU4 or later. So that means you do not need SQL Server 2016 to run this in Premise.

As well as there is a technical preview for Power BI Desktop.

And finally they have also added more functionality into the On-Premise version which is to connect live to Analysis Services Tabular and Multidimensional.

You can find the details on their blog here: Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available

SQL Server – Getting Started with Technical Preview of Power BI Reports

As with the above link, there is already a blog post explaining how to install and configure your Reporting Services to test Power BI On-Premise.

I read through this blog post and I have to say that it is really quick and simple to get this up and running. Compared to how installations were in the past, this is like a breath of fresh air.

You can find the blog post details here: Getting started with the Technical Preview of Power BI reports in SQL Server Reporting Services

Power BI – Error – Sorry, we couldn’t find that report (When opening a link from an annotated email from the Power BI Mobile App)

Today where I am contracting there was an issue when a director annotated and shared a report item from the Power BI Mobile App with a colleague (Lets call him Bob)

When Bob received the email to the report, he got the following error when trying to open the link, as shown below.

The interesting thing was that when I received the identical email I COULD access the report.

What makes it even more interesting is that the Report is part of a Group Workspace in which both myself and Bob are Admin’s.

So when Bob went into the report by going through Power BI and the Group Workspace Bob could see the report. But when Bob clicked on the link he got the error as shown above.

So after some investigation I resolved, with the details below.

How I resolved the error

What I did was to first check that Bob as indeed still an Admin in the Group Workspace.

I also went into the Members area of the Group Workspace to ensure that Bob as indeed still a member. (Just double checking everything)

At this point I was unsure as to why Bob still could not view the report via the link, but Bob could view it by going directly to the report in Power BI. Which did ensure that Bob did have access to the report.

I was going through everything and the last item I decided to check was if Bob had the Dashboard and underlying reports shared.

I went into the Share Dashboard and clicked on Access, and Bob was not in the access list.

So I went ahead and added Bob into the Access List.

After which Bob clicked on the link and he could NOW access the report from the link.

Conclusion

So in conclusion if you are going to be annotating and Sharing dashboard or report items from your Power BI Mobile App, you must also ensure that the users you are sending it to have been added as part of the Share Dashboard process.

BI-NSIGHT – Power BI (January Desktop Release, Featured Data Stories, Mobile Phone Reports Generally Available, Embed Power BI report into Salesforce, Auditing & Assign Pro Licenses with PowerShell

I cannot believe that 2017 is already where and straight out of the blocks we have already got some new features and changes to play with.

So let’s get into it and I have a feeling that this is the year that Power BI is going to dominate and become one of the leaders in the Self Service Data Analytics area.

Power BI – January Desktop Release

It is great to see that the Microsoft Power BI team, has changed the release cycle to be releasing earlier in the month.

In terms of the updates it is great to see that they have once again made some more headway with regards to the table header wrap and more on the table & matrix conditional formatting. I am confident that at some point Power BI will get to be in a similar position in terms of tables and matrix as you can do in Excel.

The new aggregations for the string and Date Time columns is really good and makes it really easy to use these now, especially with the Earliest and Latest.

As well as now Phone Reports going into General Availability so it is now available for everyone.

In terms of Data Connectivity there is a new connector for Visual Studio Team Services and an Enhanced SQL Server Connector that supports SQL Failover option.

And then finally in the Query Editor the capability to extract values from a nested list.

You can find the blog post here: Power BI Desktop January Feature Summary

Power BI – Featured Data Stories

IT is great to see how many people are submitting data stories and as you can see there is a great Sales Scorecard that was featured.

You can view this blog post showing all the data stories: Congratulations to this month’s Featured Data Stories Gallery submissions

Power BI – Mobile Phone Reports Generally Available

Whilst the option to have Mobile Phone Reports has been there, it has not been generally available until now.

This is really great to see that you have the option to create it within Power BI Desktop and ensure that when the users view the reports on their mobile phone, without the mobile app, they will get to see a consistent experience.

You can find the blog post details here: Power BI mobile phone reports now available everywhere

Power BI – Embed Power BI Report into Salesforce

This is a blog post from the Microsoft Power BI blog, in which Catalin goes through all the steps on how to configure this for your own Salesforce data.

Here is the link to the blog post: Embedding a Power BI report into Salesforce

Power BI – Auditing & Assign Pro Licenses with PowerShell

This is a blog post as well as a video with how to implement and get the Audit Logs from Power BI.

As well as to assign Power BI Pro Licenses using PowerShell.

You can find the blog post here: Using Power BI Audit Log and PowerShell to assign Power BI Pro licenses

Power BI – Quick Tips (Removing Black Line when Printing, Printing to show all graphics and Finding Custom Colours)

In this week’s blog post I am going to cover some quick tips around printing and colours. I have found that when working with real customers these are the types of questions which get asked frequently.

Removing Black Line when Printing

As you can see below, you get this black line showing when you select the Print
option from your Power BI Report in the Power BI Service.

The way to resolve this is, instead of clicking on File and then selecting Print.

You instead use your keyboard and press CTRL (Control) & P

Now as you can see below you no longer get the black line in the print dialog.

Printing to show all graphics

Following on from this, due to the way I formatted my dashboard you can see at the top it has got a white background for the Filters, Slicers and Sales Group.

This might initially appear as though it is how it should look, but when viewing the report on the Power BI Service, this is how the report looks like.

As you can see above it is missing the underlying colours, as well as some graphics.

This is due to the report having multiple levels of images and text boxes.

Now I resolved this by when bringing up the print dialog (CTRL + P) in Chrome I made sure under Options to select Background Graphics.

And as soon as I made the change it showed the print dialog as it does on the report.

Finding Custom Colours

The final tip for today is when I have been using Custom Colours and you want to use it elsewhere in your Power BI Desktop reports.

To do this I went to my visual or background where I have got a Custom Color.

Then I clicked on the drop down and then I clicked on Custom Color

This will then bring up the HEX code.

So at this point I can copy the HEX code and use it on other visuals or backgrounds.

But what I also saw was that it now appears under Recent Colors

So this made it a lot easier for me to use this Custom Color elsewhere in my Power BI Desktop.

If you have any other tips or questions please let me know.

Distribute Power BI Pro features to Power BI Free Users – UPDATED NO LONGER VALID – 25 Jan 2017

Please note that since I created the blog post, Microsoft have resolved the details below, and indicated that it was a known bug within Power BI. 

So the content below is now no longer valid – 25 Jan 2017

What would you say that as it currently stands (Jan 2017) there is a way to distribute your Power BI Dashboards and reports that use Pro features to Power BI Free License and they would be able to use and consume them without having to purchase a Power BI Pro License?

I am sure you would say “How I can do this“. I show and explain this below.

I am going to show this by creating a Group Workspace using a Pro License that I have. I will then create a dashboard in the Group Workspace. After which I will then share this dashboard with another Power BI Account which is currently has a Free License

Creating a Group Workspace (Pro Feature)

The first step is for me to create a Group Workspace and upload my Power BI File.

I go into Power BI, then click on the + sign next to Group Workspaces

Next I give my Group the required details as shown below.

Once I clicked Save I am then presented with the following screen below.

And just to confirm that it is a Group Workspace which is a Pro feature is another image I have put in below.

I then uploaded my Power BI Desktop File into the World Wide Importers Group workspace.

Creating the Dashboard in the Group Workspace

Next is where I create my dashboard in the World Wide Importers Group Workspace.

As you can see below I created a New dashboard

Once completed and made some minor modifications this is what my dashboard looked like.

Sharing my Dashboard with another Power BI Free User

Next what I did was to share this dashboard from my World Wide Importers Group Workspace with another Power BI Account which currently has the Free License.

I then got the following notification that it was indeed shared.

Confirming that my Power BI Free User can view the Dashboard

The final step is to show that my Power BI user does have the Free License and can view the Dashboard that was created in the World Wide Importers Group Workspace (Which is a Pro Power BI Feature)

As you can see below the Power BI Free user that I shared it with, does have the Free License, which is shown by the Personal Storage size of 1GB

Now when the Power BI Free user clicks on Shared with me, they will then see the Dashboard I created earlier “WWI – Sales” in their list of Dashboards.

And then finally actually opening the Shared Dashboard they see the exact same Dashboard as earlier.

Sharing with External Users

What I did find during my testing is that whilst you can share with External Users it appears to work slightly differently than my example above which is a user within the same organization.

The thing to note here is that when I logged into my Power BI Free User account I did not see any Dashboards under the “Shared with me

But I did get an email in my Inbox with a link

Once I clicked on the link I was then able to view the Dashboard.

Conclusion

I have shown above how you can use the Power BI Pro features using the Group Workspace and share the dashboard with a Power BI User account that has a Free License.

I am fairly certain that if you also had to have your On-Premise Gateway setup and configured to refresh the data for you (Another Pro feature) that the other Power BI Free User Accounts, would be able to view the refreshed data in their dashboards and reports.

I think that this currently works because when Sharing a Dashboard from “My Workspace” is currently considered a free feature, so when you are sharing a Dashboard via a Group Workspace it is effectively stillSharing a Dashboard“.

And finally we have found this to quite effective not only from a cost perspective, in that we need fewer Power BI Pro User Accounts, but that in terms of how we have been rolling out our Power BI Dashboards and solutions, it is only the Power BI Developers and required people who require the Pro license, whilst the rest of the organization can use their Free licenses, which means that it can be shared with anyone in the organization.

NOTE: This current functionality might change at any time, as is currently correct as at Jan 2017.

NOTE II: I did get a confirmation from Eoin O’Sullivan – (Twitter) that it does not work with Row Level Security.