Author: Gilbert Quevauvilliers

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)

Gilbert Quevauvilliers September 21, 2017 No Comments

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

Gilbert Quevauvilliers September 19, 2017 No Comments

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)

Gilbert Quevauvilliers September 14, 2017 No Comments

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

Gilbert Quevauvilliers September 12, 2017 2 Comments

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…)

BI-RoundUp – Power BI (Drill Through coming… – Timeline Storyteller Competition)

Gilbert Quevauvilliers September 7, 2017 No Comments

TimeTimeTHere is the weekly BI Roundup

Power BI – Drill Through coming – Already in the Power BI Service


As you can see above the Power BI Team has already enabled the Drill Through functionality into the Power BI Service.

This is something that was seen at the Microsoft Data Insights Summit and it is here.

I think that this is a really clean and simple implementation which can most definitely make the Power BI Reports more interactive.

There are two great blog posts by Dustin Ryan (SETTING UP A DRILLTHROUGH ACTION IN YOUR POWER BI REPORT) and Phil Seamark (Power BI Drill Through) which explain how to use this functionality.

Power BI – Timeline Storyteller competition

There is a blog post by the Power BI Team in which they detail how the Timeline Storyteller Custom visual came about. As well as the opportunity to contribute to the project.

Along with this, there is also a competition in which you can win some Power BI Swag.

You can find the details here: What story does your timeline tell? Introducing the Timeline Storyteller custom visual for Microsoft Power BI

DAX – Filtering Measure to show value when selected or no values selected

Gilbert Quevauvilliers September 5, 2017 No Comments

I had a requirement where I had a measure that I wanted to be filtered if it’s value was selected
then it must show the value. As well as if nothing is selected on the slicer then I also
want the value to be displayed.

I did this so that when the users are using the report, it will clearly show them what values they are looking at in the table. By making it clearly visible
once they selected a value from a slicer it is easier for the end users to get the right data.

This is best shown with an example below.

  • Current behavior is when I click on Day Shift it shows me the values for the Day Shift.
    • NOTE: In the above the 24hr Total does
      change to correctly reflect the Day Shift values.
  • Now when I click on Night Shift, I still see the values for Day Shift
    • NOTE: In the above the 24hr Total does change to correctly reflect the Night Shift values.

So below is the DAX that I used to overcome this challenge so that it would display correctly.

  • Here is the syntax with an explanation afterwards
    Day Shift =
IF (
  ISFILTERED ( 'Prod'[Shift] )
  && VALUES ( 'Prod'[Shift] ) = "Day Shift"
  || NOT ( ISFILTERED ( 'Prod'[Shift] ) ),
 CALCULATE ( [UnitType (Selected)], 'Prod'[Shift] = "Day Shift" ),
0
)

  • What is happening above is I have got an IF Statement and what I am doing is first to see if the column is being filtered. This uses the ISFILTERED and is highlighted in PURPLE above.
  • Next is where I am also looking to see if the “Day Shift” has been selected from the Slicer, this is highlighted in ORANGE
  • I put in an OR “||” Condition, and I used the NOT which is then saying it is NOT FILTERED, this is so that if neither “Day Shift” or “Night Shift” is selected then also make this part of the IF Statement TRUE, this is highlighted in GREEN
  • Now that I had completed the Condition for my IF statement I then put in my calculation if it is TRUE, which is highlighted in BLUE
  • And then finally if the Slicer has “Night Shift” selected make the value for “Day Shift” equal 0

So it is easier to see it in action as shown below.

  • Here is showing where nothing is selected on the Slicers it shows all the values.
  • Next if I select “Day Shift” I want it to show the values for Day Shift, but make “Night Shift” zero. As well as the 24hr Total should only show the Day Shift values.
  • And finally, if I select “Night Shift” I want it to show the values for Night Shift, but make “Day Shift” zero. As well as the 24hr Total should only show the Day Shift values.

Conclusion

As you can see above I have demonstrated how to leverage DAX measures so that it will display the values in a way that is meaningful to the end users of the report.

This will help them easily understand the data that they are looking at, and not have to second question or double check to see if the figures are the ones that they are expecting to see.

BI-RoundUp – Power BI (User details in Usage Metric Reports, Power BI Report Server Aug Update – Timeline Story Teller Custom Visual)

Gilbert Quevauvilliers August 31, 2017 No Comments

Here is the weekly BI Roundup of what has been happening in the last week.

Power BI – User details in Usage Metric Reports

As you can see in the image above, the Power BI team has now incorporated the capability to see which users have been accessing the reports. This is really awesome because it not only allows you to view how the reports are being used, but also allow you now to be able to understand which are the most active users.

You can find the blog post details here: Introducing per-user usage metrics: know your audience and amplify your impact

Power BI – Report Server update for August


With the August update for the Power BI Report Server, they now have enabled the capability to be able to import data from any source into the customized Power BI Desktop file for Reporting Services. This is a fantastic start at being able to use more data sources, but there are currently a few limitations which are that you cannot schedule a refresh of the data, DirectQuery is currently not supported, Scale-Out SSRS is not supported and the reports must be smaller than 50MB in size.

As well as they have now enabled you to view and interact with Excel files inside Power BI Report Server. This is done by leveraging the Office Offline Server. As with this initial release there are also a few limitations which are it cannot have any external data source dependencies, it can connect to a live connection to an SSAS as a data source and will work if it has a PowerPivot data model.

You can find all the details here: Power BI Report Server August 2017 Preview now available

Power BI – Timeline Story Teller Custom Visual

As was previously seen at the Microsoft Data Insights summit you are now able to download and utlize the Timeline Story Teller Custom Visual. I am certain that there will be some really amazing stories which will be told using this custom visual. I do hope that I can get some time to try this out with some meaningful data.

Here are the blog details here: Create interactive timeline visualizations for your data with the new timeline storyteller community visual

BI-RoundUp – Power BI (On-Premise Gateway Update August – What If Competition – Solution template for Microsoft Dynamics 365)

Gilbert Quevauvilliers August 24, 2017 No Comments

Here is this week’s BI Round up with the latest weekly information.

Power BI – On-Premise Gateway update for August

As you can see above in the August update there is now support for IBM Netezza connector the new TLS format 1.1 and 1.2.

It is also good to see that they have increased the timeout for gateway requests from 2 minutes to 5 minutes.

And finally, they have updated the Mashup engine to be the same version as the Power BI Desktop update for August, which will ensure that all your latest transformations will work with the On-Premise gateway.

You can find more details here: https://powerbi.microsoft.com/en-us/blog/power-bi-on-premises-data-gateway-august-update/Power BI On-premises data gateway August update

(more…)

Power BI – Using Change tables to compare data

Gilbert Quevauvilliers August 22, 2017 1 Comment

Have you ever wanted to compare changes in your data without having to create duplicate copies, as well as make it really simple and easy for users to do some self-service comparisons?

In this example I am going to show how I created and then used Change tables to compare data where you can select multiple items to compare.

This is best shown with an image of my completed Power BI Desktop file.

The data below is from Gap Minder in which it shows how many hours people work by country over time.

What I wanted to do was to compare based on Australia which countries work more and which countries work less. As you can see from above both New Zealand and Poland work more hours than Australia whilst Norway works significantly less.

I have also created other Power BI solutions where I have used it based on Actuals from Sales, compared to different budget versions to see how accurate the budgeting process has been.

(more…)

BI-RoundUp – Power BI (August Desktop Release – Timeline Story Custom Visual – Help Shape the Future of Power BI – Text Analysis Solution Template using Bing – Azure Activity Log Solution Template – Survey for Mobile Apps – Developer Update for August)

Gilbert Quevauvilliers August 17, 2017 No Comments

While the week before had nothing to really talk about that is new, this week there is a whole host of updates and great new things to play with so here are the latest details.

Power BI – Power BI August Desktop Release

Whilst this picture above may not look like much it is incredible with this new “What If” can do for your data and your users.

There are some great new features in the August release of Power BI desktop which I will cover below.

Reporting

The great thing that they have done with the reporting is how many updates they have made with Matrix and now you can easily put your measures on rows. This is fantastic and in the past I had to create a whole stack of measures to get this done.

You can now also use Conditional Formatting on the Font Colour, so if your data is low or high you can then put in some conditional formatting. This is again a great feature and on the way to allowing to show KPIs in the Matrix

There has also been updates and improvements for line charts, as you can see below to have custom settings for each of the line charts, as well as line marker options which means that you can customize it as to your requirements.


There has also been some new Custom Visuals added which are the Dot Plot, Power KPI, Funnel Plot and Beyond Soft Calendar which can show you ways to gain deeper insights into your data with some great ways to show what is happening within your data.




Analytics

As with the first picture one of the great new features is the What If parameter. I think that this is a killer feature as it is something business users have been asking for. As well as the implementation is relatively simple which makes it easy to integrate as well as leverage in existing Power BI Datasets.

Next is updates to the scatter chart where you can now use the Symmetry shading which will show you which has a higher value based on the X-Axis measure compared to the Y-Axis measure or vice versa

Along with this there is now also the ratio line, which is used to define the ratio of the subtotals to the x-axis and y-axis measures. Which allows you to quickly see based on either axis what your data is telling you.

There is also a new Quick measure called Weighted Average, this is where this measure calculates a weighted average of the base value for each category. This can be really handy when comparing different values.

Data Connectivity

In this month’s release connecting to the Power BI Service dataset is now generally available. As well as now you can connect to Google BigQuery which I am sure people who utilize Google BigQuery will be excited to bring their data into Power BI.

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

Power BI – Timeline Story Custom Visual

I am really excited to see that the TimeLine Story Custom visual has been released in the Office store for Power BI. This was shown at the Data Insights Summit and it showed how powerful this visualization can be. Now that it is available I cannot wait to use it on my own data to see what it can show to the users.

You can download it from the following location: Office Store – Timeline Storyteller

Power BI – Help Shape the Future of Power BI

As you can see above the Power BI team is looking for people to join the user research channel which will enable you to help shape the future by giving them your real world inputs into how and what you think is required to enable Power BI to just get better.

I have personally completed the required survey and I do hope to be selected. They do request that you complete a survey which I assume is to ensure that they are getting the right type or possibly even a good spread of people to help shape Power BI.

Here is the link to the Survey: Take our quick survey and join our research panel.

You can find more details here: Shape the future of Power BI: Join Our User Research Panel

Power BI – Text Analytics Solution Template using Bing

There is a new solution template which now allows you to use text analytics and sentiment analysis using Bing and other related Microsoft products. The difference with this solution template is that it allows you to enter your own list of words. Which was a highly requested feature. Which I think is fantastic as everyone has different requirements and this allows them to achieve this.

You can find the blog post details here: Bringing more text analytics to the Bing News Solution Template

Power BI – Azure Activity Log Analytics Solution Template

Here is another great solution template which allows you to do analytics on your Azure Logs. This is a great way to leverage all your log data so that you can see what is happening within your organization.

I quickly installed this to see if it will allow me to view my log data for what has been happening inside Power BI, and I can

PIC HERE

You can find more details here: Announcing the Power BI Solution Template for Azure Activity Log Analytics

Power BI – Survey to improve Mobile Apps

If you are interested in helping Power BI with the mobile apps, here is your change to take the Survey which will give valuable information to the Power BI Mobile Apps team.

Here is a link to the Survey: Take the Survey

And here is a link to their blog post: Help improve the Power BI mobile apps

Power BI – Developer Update for August

For the first time there is a Power BI Developer update which details all the updates and changes with regards to the Power BI Development experience relating mostly to Power BI Embedded.

There is some great content in here if you are a Power BI Developer around the Embedded content. As well as some additional details around Power BI Premium as well as how you can find code samples and even test a working version to see how it works.

You can find all the details here: Power BI Developer community August update