How to use Bookmarks in Power BI for Seamless Report Navigation

In the latest update of Power BI Desktop, there now is the capability to be able to use Bookmarks.

What bookmarks does is allow you to create an informative way in which you can tell your story. It really is an awesome feature and something that I know a lot of people have been asking for. If you want more details you can find it from the Microsoft Power BI Blog post here: Power BI Bookmarking

In my blog post today, I am going to demonstrate how to create bookmarks, and then create a home page and link the images through to specific bookmarks, which will enable a seamless navigation experience for report consumers.

I am going to be using my Strava data, and I will create bookmarks for my bike rides, runs and
swims that I have done in the past. I will then create a new Home page and link images to those specific bookmarks.

Ensuring you have got all the Bookmarking features enabled

The first thing that I did was to ensure that I had the bookmarking preview feature enabled. Then also to ensure that I could see the bookmarks pane

  • I went into the Power BI Desktop, clicked on Options and Settings and then on Options
  • I then ensured that I had a tick box next to bookmarks under the Preview Features section
    • I then clicked Ok
  • The final step was to click on View in the Ribbon and ensure that I had put a tick box for bookmarks Pane

Creating the Bookmarks

Now I will be demonstrating is how to create the bookmarks.

  • To create my bookmark, the first thing that I must do is to be on the page where I want to create the bookmark.
  • Next, if there are any slicers or filters then I made sure that I had that completed also.
    • As with my example the first bookmark that I am going to create is for Riding.
    • In my Page Filters I put in the Activity Type and selected Ride.
    • Now when I look at my report it is only showing my bike rides.
    • Then in the BOOKMARKS pane I clicked on Add
      • As you can see above it defaulted to the name of Bookmark 1
        • I then double clicked and renamed it to Bike Riding
        • NOTE: You can also click on the ellipses and select Rename
      • Once I completed my first bookmark it had the correct name.
  • I then added in the following two bookmarks.
    • Running
    • Swimming
    • NOTE: If you are looking to replicate this, I did this by simply following the steps above, but where I selected Ride I changed it to be Run and then Swim
  • Once I had completed my Bookmarks for my Activity Types I had the following bookmarks completed.

Creating my Home Page and linking it to a specific Bookmark

What I did next was to then create a home page with some images and link it to a specific bookmark.

  • I created the home page and put in 3 images, one for each activity as shown below.
  • Next to link a particular image to a bookmark I did the following.
    • I first clicked on the Swimming Image
    • Then in the FORMAT IMAGE pane, I set the Link to On
    • Where it said Type I clicked on the Drop Down and selected Bookmark
    • Then below where it said Bookmark I clicked on the Drop down and selected Swimming (From the bookmark I had created earlier)
    • So once complete for my Swimming Image it had the following link properties
    • I then did this for my Bike Image
    • And then finally I did it for my Run Image

Creating my Home Page Navigation

The final step was for me to create my Home Page navigation so when I clicked on the Home Image it would take me back to my Home Page from within my report.

  • To do this I made sure I still had my BOOKMARKS pane open, and that I was on my Home page.
    • I then clicked on Add under BOOKMARKS
  • I then renamed it to Home Page
  • I then also put the Home Page bookmark at the top of the bookmarks.
    • NOTE: The reason for this is so that when you start your Bookmarks in the Power BI Service it will start at the Home Page.
  • I went into my report page and inserted a Home Image
  • I then clicked on the Image, and went to the Link Properties.
    • I then made sure that it was linked back to the Home Page Bookmark

I then uploaded my Power BI Report to the Power BI Service.

Testing my Bookmarking

Once I had uploaded it to the Power BI Service, I wanted to test it to ensure that it is working as expected.

  • To do this I went to my report and by default I could click on the Swim, Bike or Run and it would link me correctly to the correct bookmark.
  • Likewise, I could click on the Home Icon and it would take me back to the Home Page
  • Below is what it looks like in action.
    • I am clicking through to the Bike Activities, and showing I can still interact with the report.
    • I then go back to the Home page and click through to my Running Activities.
      • Once again interacting with the data in the report.
    • And then finally going back to the Home Page

Conclusion

As I have demonstrated not only is the new Bookmarking feature great for storytelling, but it is also a feature that can be leveraged to create a seamless report navigation experience.

As always if there are any comments of questions, please leave them in the section below.

BI-RoundUp – Power BI (October Desktop Update – Update for Power BI Service & Mobile – Email Subscriptions for Apps – AI recommendation for Apps)

As with every month it is with great excitement to see what updates there are to Power BI. I have also included any other related Business Intelligence updates.

Enjoy the read

Power BI – October Desktop Update

There have been a whole host of updates to the Power BI Desktop, and here are the relevant details below.

Reporting

The biggest update in this month’s release is the availability of Bookmarks. This is a fantastic feature and now enables you to really capture that story telling with your data. It is really easy and simple to configure.

What is also a great feature is that once you have your bookmarked steps, you can still interact with the data in each step. So for instance if someone is asking details about a particular bookmark you can stop and click on items in your visuals to answer the questions.

Next is the selection pane as shown above. What this allows you to do is to either show or hide each element on your report page.

This is great both for if you want to individually get to the properties of an item on a complex page with a lot of visuals.

As well as if you want to create an impression that visuals are dynamically changing you can do this with the combination of the bookmarks.

Another feature related to Bookmarks is the ability to link an image or shape to a Bookmark.

What this then means is that you can give the feeling of navigating from a visual to a page with the filter in place. The example that the Power BI Team shows in their video is having a selection of countries. You click on a country flag and that takes you to the report showing the report with the flag that you click on. Which is actually a bookmark.

Along with the bookmarks feature is the ability to spotlight a specific chart of visual as part of your bookmarking process. This is handy when you want to give specific focus to an item on the report.

There have been some additional improvements to the Scatter charts, where now where you can change the shape type for the different scatters.

As well as they have improved how many items you can see from 3000 to 10 000.

Analytics

In the Analytics area for Power BI they have now created the functionality to be able to create Quick Measures with SSAS Live Conenctions. This is great for when there is a requirement to quickly create another measure that does not exist on an SSAS live connection.

There is also a new quick measure called “Sales from New Customers”, which allows you to see your sales from New Customers.

There is now also the capability for when you have created Cell-Level formatting in your SSAS instance. This will now be honoured when you bring the data into your Power BI Model.

Data Connectivity

There is a new connector for Vertica, which if you use it you can now leverage it inside of Power BI.

There has also been updates for the SAP BW Connector with additional member properties.

And finally, there has been improved Help access from within Power BI Desktop. There is now additional items on the Help Ribbon in Power BI Desktop.

You can find the video as well as all the other details here: Power BI Desktop October Feature Summary

Power BI – Email Subscriptions for Apps


As you can see above on the left-hand side is the option to subscribe to a Dashboard in my App. And on the right-hand side is the option to subscribe to the report in my App.

This now allows your users who have access to Apps, to now be able to get the information in their Inbox daily.

You can read about it here: Introducing email subscriptions for Power BI apps

Power BI – Monthly Service & Mobile Update

There was a whole host of updates to the Power BI Service in the past month, some of which I was unaware of until reading the blog post. So please find all the relevant details below.

You can now share Dashboards with Free Power BI Users if your App Workspace is on a Premium Capacity. This is great, because before this update you could only share Apps with Free Users.

With Power BI Premium, there now is the option to change how you want to allocate your V-Cores. If you have a larger Premium capacity you can then modify the settings so that you could allocate it to a smaller SKU.

Leading on from the above is you now can very quickly in one click scale up or scale down your Capacity. Once again this is based on the number of V-Cores that you have purchased.

Now there is also the capability to use PubNub Blocks to push data into the Power BI Rest API

There was also an update to the Power BI On-Premise Gateway with an updated version for Personal mode which is in line with the Enterprise mode. The support for Government clouds in Personal mode. As well as the Gateway now has the September update for the MashUp Engine.

You can now also filter reports in the Power BI Mobile App, which is something that is often needed because you only want to see the data you are interested in.

There is now support for Visio across all the platforms for Power BI including the Web as well as mobile platforms.

And finally, there was an update rolled out for improved performance for the Usage Metrics, which in the past did take a while to load, but now is a LOT quicker.

You can read up about it all here: Power BI Service and Mobile September Feature Summary

Power BI – AI Recommendations for Apps

Now when you search for Apps within your Organization it will use the AI engine to suggest what other Apps might be of interest to you.

Not only will this allow you to be able to potentially have insights to data that you did not know were there. But it also honours the Security that is put in place for access to Apps. If you do not have access to the App, then you will not see the recommendation.

You can find all the details here: Announcing AI-powered app recommendations: getting the right insights to the right people

Using Power BI to ensure Daylight Savings Dates and Times change dynamically

I was helping on the Power BI Community and it struck me that there currently was not a way to easily manage Dates and times for Daylight savings. In this blog post below, I will show how I achieved this by using parameters and little bit of M magic (Directly Editing the M Code).

As part of completing this solution I also included the UTC Offset, which I did blog about previously Power BI – Did you know ALL Power BI Services Servers are in UTC? Now how to handle it for Dates & Times which will ensure when the Power BI Desktop file is uploaded and refreshed within the Power BI Service it displays the correct Date and Times.

(more…)

Power BI – Did you know ALL Power BI Services Servers are in UTC? Now how to handle it for Dates & Times

I was working for a customer recently, where the requirement was to create a slicer which would only show the data for Today, Yesterday and then all other days.

When I was consulting with the other developers they were unaware that the Power BI Services Servers all have the Time zone set to UTC.

And this applies to ALL Power BI Services Servers, no matter which data center they are located worldwide. This was an unknown fact. And I have had this confirmed by the Power BI team.

In the steps below I will demonstrate the Times for the Power BI Server Services.

And then I will demonstrate how I achieved the Today, Yesterday and other days slicer, so that it would display correctly in the Power BI Service for the local users.

Power BI Service Times

I am currently living in the Gold Coast in Australia, which is UTC + 10. And below I will demonstrate when I uploaded my Power BI model to the Power BI Service, how the times were different.

As you can see above the time in UTC (Power BI Service) was 7:51 PM on 02 Oct 2017, whilst where I am it was 5:51 AM on 03 Oct 2017

NOTE: If I wanted to find out what my UTC Time Zone was I would go to the following URL to find out: https://www.timeanddate.com/worldclock/

  • I could then type in where I was living (Brisbane), which then resulted in the Time Zone being shown.

What I did to get the above times is I created one measure which is based on the Local Time of the Server (Date Time of Power BI Service) and I created a table in the Query Editor in which I took the Local Time of the Server and added 10 hours (Date Time where I am (UTC + 10)

Creating the Today, Yesterday and Other Days Column – Which I then used as a Slicer

In the steps below I will demonstrate how I created a new column in the Query Editor which would show Today
(for my current date), Yesterday (for the date going one day back) and Other Days for all other days.

  • I went into the Query Editor and to my Date table.
  • The first thing that I did was to create a column called Current Date, which is my current date
    • As you can see above I added a Duration of 10 hours to the FixedLocalNow().
    • I then also used the Date.From so that it only returned the Date
    • NOTE: As I explained previously this is because when I upload it to the Power BI Service I will need the Date Time to be at my local time zone which is UTC + 10
  • I then added a Conditional column to see if my column called “Date” equals my “Current Date” and if it is then it is the “Current Date” otherwise “Other Date”
  • I created another column for Yesterdays Date
    • As you can see above, the syntax is very similar, with the addition of going back one day.
    • I added a Duration of 10 hours to the FixedLocalNow().
    • I then also used the Date.From so that it only returned the Date
    • As well as the Date.AddDays so that I could go back one day.
    • NOTE: As I explained previously this is because when I upload it to the Power BI Service I will need the Date Time to be at my local time zone which is UTC + 10
  • This then allowed me to create my final conditional column in which I then defined if it was Today, Yesterday or Other Day
  • I then loaded this into the Power BI Data Model.

Testing the Date Time in the Power BI Service with UTC Time Zones

I then uploaded my Power BI Desktop Model to the Power BI Service, to ensure that it would work for my local Date Time zone.

  • Next to test it I created a Slicer off the column called “Today/Yesterday – Other Days”, and then put in all the Dates from the Calendar table to see if it worked as expected.
  • When nothing was selected on the Slicer it showed all the Dates.
  • Next when I clicked on Today in the Slicer it showed me Todays Date – 03 October 2017
  • Next when I clicked on Yesterday in the Slicer it showed me Todays Date – 02 October 2017

Conclusion

I have demonstrated in this blog post how the Power BI Servers are in UTC.

You can test it out below.

As well as shown how to ensure that when my data refreshes, and is used within the Power BI Service it will be relevant to my Time Zone.

If you have any comments or questions please leave them in the section below.

BI-RoundUp – Power BI (Enterprise Capabilities for Power BI – Premium Click Through Demo – Campaign/Brand Management for Facebook – Power BI Report Server Roadmap – Understanding Power BI SKUs) – SQL Server 2017

With Microsoft Ignite happening this week there have been a whole host of announcements to Power BI which is great to see. So please read below for all the additional updates and details.

(more…)

Power BI – Using OneDrive to Embed and Host Images for Power BI

I was recently on the Power BI Forums and I have seen a question before around where and how to host images that can be used within Power BI.

I am going to show this with the example below.

  • I am going to show you how to upload images to OneDrive, which can host your images for free.
  • Then I will show you how to get the Embed Codes
  • And finally put it all together in a Power BI Desktop file, so when it is viewed in either Power BI Desktop or the Power BI Service, I am then able to view the images.

(more…)

BI-RoundUp – Power BI (On-Premise Sept Update – Developer update for Sept – Congrats to What-If Winners – Custom Connectors in Action – Timeline Storyteller custom visual contest)

There have been quite a few updates in the past week, so please find all the related updates below, of which there have been quite a few!

Power BI – On-Premise Sept Update

In this month’s update for the On-Premise Gateway they have added support in the Personal mode for national clouds.

There has also been a great improvement in the Gateway configuration experience which to me personally is almost identical to the On-Premise Gateway, which is great to see and makes the whole experience a lot easier and smoother to get up and running.

And finally, an updated version of the Mashup Engine to match the September update of Power BI Desktop.

You can find the details here: On-premises data gateway September update

Power BI – Developer Update for September 2017

As you can see above there have been some updates with regards to Power BI Developer and Power BI Embedded.

  • You now can also clone tiles and dashboards between different App Workspaces.
  • There is now also the capability to use Row Level Security (RLS) for Analysis Services (AS) on-premise data sources when using Power BI Embedded.
  • Row Level Security (RLS) has got 3 additional properties as part of the GenerateToken within the API
  • There is now the capability to be able to export or clone a PBIX file from the Power BI Service. This will allow you to either export it into a file system or use the API to create a clone and upload the same PBIX file to another App Workspace.
  • And finally, there is the ability to change the language and text formatting of your embedded content

You can view the details here: Power BI Developer community September update

Power BI – Congratulations to the What-If Parameters Winners

Congratulations to the winners in the What-If contest. There were some great entries and I have to say some really amazing idea’s

You can find out who won, as well as all the other entries here: Congratulations to the winners of the What If contest!

Power BI – Custom Connectors in Action

As you can see above by creating Custom Connectors for Power BI you now can connect to almost any data source.

With the example above Miguel Escobar created a Custom Connector to Meetup, which is used for all the Power BI User groups, and it is great to see how the number of members has steadily increased.

On a side note it is fantastic to see that the Power BI User Group that I co-organize with Craig Bryden is 4th overall!

You can find the blog post details here: Power BI in action: using custom connectors

Power BI – Timeline Storyteller custom visual contest

As with the previous weeks announcement with regards to the Timeline Storyteller custom visual, there is now a contest in which you could win some great prizes. I am looking forward to looking at the entries, especially because this Custom Visual is so powerful there could be a whole range of amazing reports.

If you are interested or want to view the entries here is the blog post: Don’t miss the Timeline Storyteller custom visual contest!

Power BI – Creating Dashboard or Report Email Subscriptions

I have seen a few questions asked in the Power BI Community Forum with regards to why people are not receiving their Email Subscriptions (Dashboard or Report). So I thought it would be best to create a quick blog post to hopefully help a few people.

So below I will show how to create the subscription as well as some tips along the way to ensure that it will work for you as it is currently doing for me.

With the example, I am going to be creating a Dashboard Email Subscription, but the same can be done with a Report Email Subscription.

NOTE: For Dashboard or Report Email Subscriptions to be emailed you have to have data that is being refreshed at least once a day. If it is not being refreshed it will not trigger the Dashboard or Report Email Subscription.

Creating an Email Subscription (Dashboard or Report)

In the steps below I will explain how to create a dashboard email subscription.

  • I logged into the Power BI Service and went to my Dashboard where I wanted to receive my Dashboard Email Subscription.
  • Then in the area on the top right-hand side I then clicked on Subscribe
  • This then will open the Subscribe to emails window as shown below.
    • As you can see above it has got the name of my Dashboard (FourMoo).
    • But one VERY important tip that I want to draw your attention to in the above image is that next to the dashboard name it says Draft
      • Now what this means is that whilst it appears that the subscription is set, it is actually not due to it still being a Draft. I would like to think of this being an email Draft, the email might be complete but it is not yet sent.
      • And that is indicated by the above
      • I think that this is where a few people think that once they see this window the Email Subscription is enabled. It is not currently set.
  • Now to set or apply my Email Subscription I needed to click on Save and Close at the bottom of the Window as highlighted below.
  • Once I clicked on Save and close I got the confirmation that my Subscription was set.

Checking to see my Email Subscription (Dashboard or Report) is set

Next I will show you how to ensure that your Email Subscription is set, which ensured that it did get emailed to me.

  • The best place where I could view my Subscriptions was to go into my settings
  • Then I clicked on Subscriptions
  • As shown above I can see that I have got a Dashboard and a Report Subscription.
    • There is an option to Edit my subscriptions under the Actions area.
  • I could also go and view my subscription by going back to my dashboard and then clicking on the Subscribe button again.
    • This then brought up the window as before as shown below.
      • But as you can see this time there is no Draft
        in the image above. Meaning that it has been set.

Conclusion

As I have shown above is how to create an Email Subscription (Dashboard or Report). As well as how to check that it has been set.

Any questions or comments please leave them in the section below.

BI-RoundUp – Power BI (Desktop update for September – Service update for August – Filters coming to iOS App – How Feedback influences Microsoft Business Applications Development)

This is the weekly update in the world of BI

Power BI – Desktop update for September

We now have got the September release of Power BI Desktop and there have been almost all of the updates in the reporting area within Power BI Desktop, so as I do with every monthly update I will give an overview below.

Reporting

As you would have seen above as well as in my previous blog posts is that there now is drill through capabilities in Power BI. This is something that was shown at the Data Insights Summit and now is available and ready to use with the September edition of Power BI Desktop.

There is now also as part of Power BI Desktop is the functionality to right click on selected area’s which will enable you to select the “Analyze” and “Explain the increase/decrease”. This is still in preview so please use the buttons as well as feedback to let the Power BI team know what works for you and what does not.

Please also find below the current limitations with regards to the “Explain the increase/decrease” – Which was kindly provided by the Microsoft Power BI Team.

Here is the link to the documentation: Use insights in Power BI Desktop (Preview)

You can’t use it if your visual includes:

  • TopN filters
  • Include/exclude filters
  • Measure filters
  • Non-additive measures and aggregates
  • Show value as
  • Filtered measures (it’s the new thing we use for scatter chart in insights)
  • Categorical columns on X-axis unless it defines a sort by column that is scalar. If using a hierarchy, then every column in the active hierarchy has to match this condition
  • Non-numeric measures: Model measure support is limited to sum and count only right now.

There are also limitations in data connectivity:

  • Direct query
  • Live connect
  • On-prem RS
  • Embedding

NOTE: The Power BI Team is actively working to expand the support in the future

There is now also a new Ribbon chart, which I am using already, as it makes it really easy as well as insightful to explain the changes in your data over time. I think that a lot of people will find this new visual really handy to easily show the report consumers what story their data is telling them. As you can see below I had an increase in traffic from Canada towards the end of August 2017

There has also been a theming update, and I am not going to go into all the different aspects, but I will bullet point the additional theming options now available.

I personally think that this will go a long way in Corporate Organizations where they want to ensure that all their Power BI reports adhere to their corporate policy.

  • You can set the default font for all visuals.
  • You can set the default font size for all visuals.
  • Turn data labels on for all visuals
  • For selected visuals you can also set the defaults.

With regards to accessibility there are additional improvements which is great to see, and I am by no means an opposition to Power BI expert, but I have not seen this functionality in any other Data Analytics products, and it is something which should be built into products. It is great to see that there now is the keyboard shortcuts, as well as when using the Keyboard Shortcuts it highlights the associated data better for easier readability.


Another update has occurred for the High-Density Scatter chart sampling. And for this what they did was where the points were densely populated you cannot see any real difference because all the scatters are so close together that it makes it look like one solid piece. So instead they reduced the number of scatters where it is so dense, which then allowed the algorithm to show more of the outliers.

As you can see on the first image is with the previous algorithm, whilst on the second image is with the new algorithm, and I can see quite a few more outliers, especially for the United States.


Another smaller yet really useful feature is the cartesian gridline control, which now allows you to control and style the gridlines. As you can see below this can be useful on dark themes.

There has also been some new Custom Visuals as shown below, which you can access from the Office Store.

Calendar by Tallan

Enlighten Aquarium


Impact Bubble Chart

Data Connectivity

In terms of data connectivity there is now an Azure Consumption Insights connector as well as improvements for the Dynamics 365 for Financials connector.

You can find the detailed blog post and video here: Power BI Desktop September Feature Summary

Power BI – Service Update for August

There have been a whole host of updates to the Power BI Service that happened in August.

As you can see above the usage metric reports was updated so that it now includes the users who are viewing the reports.

Now you can use Power BI Apps to leverage some of the existing pre-built packages, and currently the people at the Power BI team have started with the following Google Analytics, Workday and Marketo, with more planned.

There has also been an update to the iOS app, so that users can now use Proxy settings which will enable them to view Power Bi via their VPN.

There was also an update to the Power BI On-Premise Gateway with some new connectors and the same version of the Mashup Engine as the August release.

You now can use Dynamic Row Level Security with Power BI Embedded.

And finally there are updates to the Custom Visuals API which is now on version 1.7

You can find the details here: Power BI service and mobile August 2017 feature summary

Power BI – Filters coming into iOS App

As you can see above filters are coming first to the iOS app.

This is great to see that they have made it so easy to use it on a mobile platform.

I am fairly certain that this would later be coming to Android and Windows platforms.

You can view the blog post here: Filters coming for phone reports on iOS

How feedback influences the Microsoft Business Applications development

This is a great blog post in which they explain not only how they receive feedback from customers, but also how they use it within Microsoft.

I think it is great to see that all the answers are actually read, and can influence the product in terms of what they develop as well as priority.

You can read about it here: How user feedback influences Microsoft Business Application development

Power BI – Creating Text based on TOPN values from Filters ordered by measure value

When I was working on the What-If contest, one of the requirements that I wanted to add was text based details. But the kicker was that when I put the text values into a measure it was showing the values, but based on an alphabetical order instead of being based on the numerical values.

One challenge for me was to not only return the data in the correct order, but to also return the Text based data, as I could easily do it with standard measures.

As with most of my blog posts it is best shown with an example.

  • Below you can see that I used the default Concatenate, using the TOPN 3, and as shown below the order of the crimes go from Fare Evasion, Weapons and Miscellaneous Offences
  • But what I wanted was the text to show it in the order of the Visual above, which should be “Fare Evasion, Weapons, Miscellaneous Offences, etc”

(more…)