Power BI – Turning off (Disabling) Native Database Queries

In this quick blog post I am going to show how to turn off or disable having the dialog
box which pops
up when using Native Database Queries.

This can sometimes be an extra step on something that you have to action when refreshing your data either in the Query Editor or in Power BI Desktop.

NOTE: I was often prompted with the following below for EACH native database query.

As you can see below this is typically what would happen if I ran a native database query

• I would first get the yellow car asking for permissions to run the native database query.
  • 
• Then once I clicked Edit Permission I then got the following Window, showing me the query that is going to be run.
  • 
• Once I click Run it would then run the query.
• So this can be time consuming especially if I had multiple queries.

How to turn off or disable the Native Database Queries

In the steps below I will show how to easily turn (Disable) the native database queries

NOTE: I could complete the steps below from either being in the Query Editor or within Power BI Desktop.

• Click on File and then Options and settings and then Options
  • 
• Then under GLOBAL I clicked on Security
  • 
• Then in the Security settings at the top it has got a section which says Native Database Queries you will see an option.
  • And there is a tick
    next to “Require user approval for new native database queries“
  • I removed the tick.
  • 
• Then click
  Ok.

Now I went back and ran my native database query and it simply
ran with no requests to Edit Permissions or to Run the query.

Conclusion

I have shown how to disable the native database queries which not only helps when I am developing but also assists me when my Power BI Desktop file is refreshing.

Updated Power BI Free vs Pro Infographic

With all the changes to Power BI, I have updated my original Power BI Infographic so that it reflects all the changes.

As you can see below, there are still some features which you can only use as part of the Pro license.

As always if you have got any comments, or if I have left something out, please let me know and I will be happy to update it.

BI-RoundUp – Power BI (June Desktop Update – Data Insights Summit Details – Power BI Embedded Pricing & Details – Power BI Premium Generally Available – Power BI Report Server Generally Available)

I was fairly certain that there were going to be a whole host of updates and additions to Power BI, and I can say that I am not disappointed. There is a whole host of information below, where I will endeavour to try and get it all to you, so this might be a bit long to read, but it will be worth it.

Power BI – June Desktop Update

First off is another big update to Power BI Desktop and as I do every month I will highlight the new features.

Report View

As with the image above you now can add in data bars for tables and matrixes, this is really handy and as they are continuing to do, is to add in Excel like functionality into Power BI

Next is the ability to add in Line Markers for your charts. Which can make it visually easier to see, but as they mention also easier for color blind people. A thing to note is that currently (June 2017) it is limited to Categorical Axis, and the Continuous Axis will be coming in a later release.

You can now also change the fonts in the formatting pane for your charts in the following area’s below (The rest of the area’s will be added in future releases)

• Axis Labels
• Axis Titles
• Legend Values
• Data labels
• Every section of the new Table and matrix visuals
• Slicer header and items

They have also now enabled the ability to use a Horizontal Image slicer to your report. This makes it easier and more flexible so that you can now use this in your report as required.

You can now use the Combo chart to align the zeros in your data, as well as shading so that it all aligns to show the data as accurately as possible.

It is nice to see that you now also have got some updates to the Bing maps being used in Power BI, as well as they are going to leverage the new Bing API which is going to be released at the end of June.

Also in this update there is now a new algorithm for high density line sampling, I know in the past that Power BI has been called out that it cannot sample enough data points, so now the Power BI team has updated the algorithm so that it now will take each point, and put it into bins, and then takes the min and max which it will then plot into the data. This ensures that you will not only get a more accurate visual representation of your data. But you will also get to see the outliers.

Note that the new High Density Sampling option is enabled by default.

The last part for the reporting update is that there now is the ability to be able to create Accessible Reports. They are starting with using Keyboard navigation and as well as you can now add Alt Text to all the visuals, so that when people who require accessibility options there will be some additional text for them to understand what the chart is telling them. The Alt Text will be read out by the accessibility options.

Data Connectivity

With the previous months release of custom data connectors. They have now enabled it as a preview feature so that it can now appear under the Get Data when you open Power BI Desktop.

They have also released the following new data connectors

• Power BI service Live Connect to On Premises and Push streaming datasets
• Impala connector is now generally available
• Amazon Redshift connector is now generally available
• SAP BW connector – DirectQuery support (beta)
• IBM Netezza connector (beta)

Query Editing

They have updated the “Add Columns from Examples” so that it now is giving you multiple suggestions for what you are trying to achieve. I personally think that this in incredible because it now means you can choose which one you want to use when you can have more than one outcome.

You can find the blog post from Microsoft here: Power BI Desktop June Feature Summary

Data Insights Summit Details

With the Microsoft Data Insights Summit that is currently happening in Seattle the Power BI team has been really busy letting us know what features are coming and what to get excited about in the coming months, so below is a quick overview of what has been released.

What is coming

As you can see below there are a whole host of details around what is now available and what will be completed in the next quarter.

Drill through Pages

This is one of the announcements that have created quite a bit of excitement, in that very soon you will have the capability to be able to drill through to other pages. But not only that when you do drill through it will remember what you selected or filtered and use this on your drill through page. So now you can drill through, as well as go back to your previous page.

I personally have been asked about this before and I know that it is something that people have been waiting for. As well as this can make the entire reporting experience a lot better and interactive.

What-If Analysis

This is a really amazing feature in that you can quickly and easily create a What-If scenario and then use this within your data to see how it will be affected.

Bookmarks

Another new feature which will be released soon is the capability to create bookmarks. You can then use the bookmarks to do story telling with your data. And you can enhance the bookmarks that you have saved, so that you can make things pop out or highlight certain aspects.

What I really am excited about is that you can still interact with your data in your bookmark. So it is totally interactive, and as they say this is the Power Point for Data Insights.

M Language

As you can see below M language is going to have more integration with Office products, as well as versioning & dependency tracking for extensions which appears to be to have version control!

Power BI Desktop Version Control

You can use OneDrive for Business to ensure that you have got version control for your Power BI Desktop files

Organizational Folders

It appears that you will now be able to organize your folders as you could previously do with SSAS OLAP

Power BI Write back with Power Apps and Visio Custom Visual

This is a big one, where they have now used Power Apps in order to facilitate a write back of your data into your Existing Power BI Model. This is something that people have been asking for, for a long time.

As well as now you can also integrate Visio visuals into your Power BI report, and if you create your Visio visuals with ID’s you can map these to your data so that they will interact as you can currently do with all other visuals within your report canvas.

Azure Analysis Web Designer

There is a preview coming where you can now design and tune Azure Analysis Services from within your Web Browser.

The things to take a note of is that you can import Power BI Desktop files which will be converted into Azure Analysis Services, which means you can create a proof of concept locally and then enable it to be used for the enterprise.

As well as then integrate these datasets with Power BI, Excel, Visual Studio and GitHub, so if you are looking for more source control around your development this might be the option going forward.

Power BI Embedded Pricing & Explanations

There now is a white paper in which they explain how to leverage Power BI Embedded with Power BI Premium. As well as also detail what the pricing is and also explain how page renders work, which is important for when deciding which size to go for.

Here is what the pricing points are below.

You can download the White paper from the following location: Plan capacity for embedded analytics with Power BI Premium

Power BI – Premium Generally Available

Power BI Premium is now generally available, so organizations who are looking to leverage the capacity or to significantly save on licensing costs can now purchase Power BI Premium.

You can get more details here: Power BI Premium

Here is also some additional documentation around Power BI Premium:

• How to purchase Power BI Premium
• Manage Power BI Premium

Power BI – Power BI Report Server Generally Available

As you can see from above, which they also announced at the Microsoft Data Insights Summit, Power BI Report Server is now Generally Available. This is great news for people who are going to purchase Power BI Premium, or they have the correct SQL Server Licensing model to install this.

You can find the details here: Power BI Report Server now generally available

Data Insights Summit Details – Day 1 Summary

With the Microsoft Data Insights Summit that is currently happening in Seattle the Power BI team has been really busy letting us know what features are coming and what to get excited about in the coming months, so below is a quick overview of what has been released after Day 1.

What is coming

As you can see below there are a whole host of details around what is now available and what will be completed in the next quarter.

Drill through Pages

This is one of the announcements that have created quite a bit of excitement, in that very soon you will have the capability to be able to drill through to other pages. But not only that when you do drill through it will remember what you selected or filtered and use this on your drill through page. So now you can drill through, as well as go back to your previous page.

I personally have been asked about this before and I know that it is something that people have been waiting for. As well as this can make the entire reporting experience a lot better and interactive.

Organizational Folders

It appears that you will now be able to organize your folders as you could previously do with SSAS OLAP

Power BI Write back with Power Apps and Visio Custom Visual

This is a big one, where they have now used Power Apps in order to facilitate a write back of your data into your Existing Power BI Model. This is something that people have been asking for, for a long time.

As well as now you can also integrate Visio visuals into your Power BI report, and if you create your Visio visuals with ID’s you can map these to your data so that they will interact as you can currently do with all other visuals within your report canvas.

Azure Analysis Web Designer

There is a preview coming where you can now design and tune Azure Analysis Services from within your Web Browser.

The things to take a note of is that you can import Power BI Desktop files which will be converted into Azure Analysis Services, which means you can create a proof of concept locally and then enable it to be used for the enterprise.

As well as then integrate these datasets with Power BI, Excel, Visual Studio and GitHub, so if you are looking for more source control around your development this might be the option going forward.

BI-RoundUp – Power BI (Service Update April & May – Personal Gateway Update – How to Purchase Power BI Premium – Managing Power BI Premium – Memory Consumption in Power BI File – Power BI – Exposing M Code)

It has been a busy week with the start of another month leading into June, so here are the updates. I am pretty confident that with the Microsoft Data Insights Summit happening Monday & Tuesday of next week that the new version of Power BI Desktop, as well as some new features will be revealed then. And I cannot wait, I always look forward to the start of each and every month.

Power BI – Service Update April & May

Due to this update being for almost 2 months there have been quite a lot of updates in the service, which I will highlight the significant ones below.

Already announced and available in the Power BI Service, is now the ability to be able to view usage metrics for your dashboards and reports.

The biggest update is now the capability to be able to pass multiple URL parameters as a filter.

I know that this is something I have personally been asked in the past and will be very welcome.

Also there is now support for PostgreSQL, the Power BI SharePoint Web Part is available for All Users, the Data connectivity SDK, New Service Content Packs as well as Power BI Apps Public Preview.

You can find out all the details here: Power BI Service April and May Feature Summary

Power BI – Personal Gateway Update

There has been a new release for the Personal Gateway, which has now got the following new features.

• Delete a personal gateway from the Power BI Service
• Improved performance
• Configuration and service logs

The personal gateway appears to be moving towards a lot of the functionality that there currently is in the On-Premise Gateway, which is great because it will now mean that it is reliable, as well as having troubleshooting tools, which I feel is essential when there are issues, and can help quickly find the problem.

You can find the details here: On-premises data gateway (personal mode)

Power BI – How to purchase Power BI Premium

Even though Power BI Premium is not officially been released yet, there is already content out there in terms of how to go about purchasing Power BI Premium when it becomes available.

You can find all the related details here: How to purchase Power BI Premium

Power BI – Managing Power BI Premium

This is another blog post, where Adam Saxton explains and goes into details around how to manage Power BI Premium.

There is some great content and one of the things that I am looking forward to is the ability to be able to assign specific users to the Premium capacity. As well as even if you wanted to only assign specific App Workspaces to the Premium capacity. I think that this will also make it really flexible for different options within your organization.

You can read up about it here: Manage Power BI Premium

Power BI – Memory Consumption in your Power BI Desktop File

This is a fantastic blog post by Imke Feldmann where she shows how to leverage the internal DMV’s within Power BI Desktop to reveal the memory consumption of your Power BI Desktop File.

One thing to note is that I think you can only have one Power BI Desktop file open in order to get the DMV’s. This is because of how in her code, she goes and looks for the Port ID, and if you have more than one file open it will not be able to know which Port ID to use.

I have emailed Imke Feldmann to confirm that you should only have one Power BI Desktop file open at a time.

In her blog post she explains how to get this data, by downloading the Query Editor M code and then pasting it into a table (which you create by using Get Data, then Blank Query)

Her blog post details are here: Analyse your memory consumption in PowerBI

Power BI – Exposing M Code

This is another really interesting blog from Reza Rad, where he leverages off one of my existing blog posts (What makes up a Power BI Desktop PBIX File) and goes into more details around the DataMashup file.

I honestly did not know that it was also a ZIP file, so I learnt a great deal from this blog post, to understand how the DataMashup file works. But this will also enable me to be able to go and possibly use the M code from within the Query Editor into other Power BI Desktop files where I need an exact copy of the M Code.

You can find Reza’s blog post here: Exposing M Code and Query Metadata of Power BI (PBIX) File

DAX – Getting the Previous Year to Date Value up to and including the Current Month Selected – Not the entire Previous Year

So for this example it is best explained with an image below, then some additional context afterwards

• If you look above we have got the [Sum of Sales] for Dec 2010-11
  • NOTE: This I selected in the Slicer on the right hand side.
• Then I have created a Year to Date (YTD) measure which is the running total for the Year
  • NOTE: This is the measure below.

    YTD-MTD-CY = TOTALYTD([Sum of Sales],’Date'[Calendar Date],”06/30″)

• As you can see for Dec 2010-11 with the measure [YTD-MTD-CY] I have the Year to date value.

Now the requirement was if the user selected any Fiscal Month (EG: Feb) from the Slicer, they are looking for the Feb 2009-10 value for the Previous Year (Feb 2009-10). So once again if I show this in a picture this is the value that I am looking to get.

DAX Measures

Below are the DAX measures that I used with an explanation afterwards.

• DAX Measure

  YTD-MTD-CY =
  TOTALYTD ( [Sum of Sales], 'Date'[Calendar Date], "06/30" )

  

  • What this measure is doing, is getting the YTD total for the current Fiscal Year for the [Sum of Total Liability.]
• DAX Measure

  ZCALC - YTD-MTD-PY =
  CALCULATE (
    [YTD-MTD-CY],
    PREVIOUSYEAR ( 'Date'[Calendar Date], "06/30" ),
   'Date'[Fiscal Month] = VALUES ( 'Date'[Fiscal Month] )
  )

  

  • This is the DAX measure that does all the work required to get the result that I wanted.
  • The first thing is that I used the previous measure [YTD-MTD-CY]
  • Next in the filters, the first filter that I used was to go back to the PREVIOUSYEAR highlighted in BLUE
    • Again with the PREVIOUSYEAR I am ensuring that it goes back 1 year, based on the Fiscal End Month.
    • NOTE: So in the current context it will now be going back the entire Previous Year, and not stopping at the month selected.
  • The final filter that I added is where I am filtering the Previous Years values, to stop at the value selected based on what was selected on the slicer, highlighted in ORANGE.
    • So when a value is selected on the Slicer it will return this value to the filter.
    • And then instead of the values going to the end of that current fiscal year, it will stop at the Fiscal Month Selected.
• DAX Measure

  ZCALC - YTD-MTD-PY Blank =
  CALCULATE (
    [YTD-MTD-CY],
    PREVIOUSYEAR ( 'Date'[Calendar Date], "06/30" ),
    'Date'[Fiscal Month] = "Jun"
  )

  

  • This measure that I created above, is so that if nothing is selected on the Fiscal Month Slicer, then return the last month of the Fiscal Year which is Jun.
  • The measure is identical to the one above, but I just hardcoded the Fiscal Month Value.
• DAX Measure

  YTD-MTD-PY =
  IF (
    HASONEVALUE ( 'Date'[Fiscal Month] ),
    [ZCALC - YTD-MTD-PY],
    [ZCALC - YTD-MTD-PY Blank]
  )

  

  • This is the final measure, where I evaluate if a value is selected on the Slicer (TRUE), then use the measure [ZCALC – YTD-MTD-PY]
  • If nothing is selected (FALSE) then use the measure [ZCALC – YTD-MTD-PY-BLANK]

Results

So now I have the measure that I need with the correct results showing below.

• As you can see above Aug was selected in the Fiscal Month Slicer.
• So when you view the top table, you can see that the YTD value is $491,100,640 for Aug 2010-11
• Then when you look at the bottom table you can see that for the measure
  [YTD-MTD-CY] is shown correctly for 2010-11
• And then when you look at the bottom table for the measure
  [YTD-MTD-PY], this is showing the Previous Years
  value on the row for 2011-12

Conclusion

So as you can see above, when you change the value in the Fiscal Month Slicer, it will then correctly update the measure to show up to and including the Fiscal Month
selected for the Previous Year.

And here is what it looked like when completed when I selected Nov

And then once again when I selected Mar

BI-RoundUp – Power BI (Usage Metrics – Mobile Apps May 2017- Stream Data Insights Summit – Processing Suggestion Idea’s for Power BI)

Here is this week’s BI-RoundUp with some great content below.

Power BI – Usage Metrics

You now have the capability to be able to view the usage metrics for your dashboards and reports in Power BI. This is a great feature, because it now gives the organization the capability to see how well the dashboards and reports are being used, shared or viewed.

In order to be able to view the metrics you need to have a Pro license, as well as at least have Edit access to the Dashboard or report to be able to view the Usage metrics.

There is also the option to disable the usage metrics for the Organization in the Admin Portal.

And finally you can also build your own Usage Report, by clicking File, Save As, and from there you are then able to modify the existing report so that it can suit your requirements.

You can find that and more details here: Usage metrics for dashboards and reports

Power BI – Mobile Apps Update for May 2017

With the latest update from the Mobile Apps team, you now have the option to use the Shared With Me, to easily see the content that was shared with you on your mobile phone.

As well as the ability now to consume Apps that have been created within your organization. You can either get this from the AppSource, or by using the link provided when the App is created.

You can also connect to the On-Premise Power BI Report Server. And finally for iOS you can share reports and dashboards with Universal links.

The blog post details are here: Power BI Mobile apps feature summary – May 2017

Power BI – Stream Data Insights Summit

As you can see above there is the ability to be able to stream the 3 keynotes, as well as 9 of the most popular sessions.

You can find the link here to be able to gain access: Watch live from the Microsoft Data Insights Summit

Power BI – Processing suggestion idea’s for Power BI

As I am sure in the past we all have voted for an idea at ideas.powerbi.com and in this blog post from Charles Sterling he explains how they go about processing the voted idea’s.

As well as what the different statuses mean when viewing existing idea’s

You can find the details here: Processing your suggestions on Ideas.powerbi.com

BI-RoundUp – Power BI (Office 365 Adoption Content Pack – Embedded Accelerator) – Excel (Get & Transform Updates May 2017)

This will no doubt be a quieter week, leading up towards the end of May and into June when there will be the next wave of updates from the Microsoft Power BI Team

You might also notice that I have changed the name from BI-NSIGHT to BI-Roundup, as there was another blogger who has his blog URL as BiInsights, so rather than make it confusing I thought to change the weekly update name.

Power BI – Office 365 Adoption Content Pack

There is a new content pack for Power BI, which will leverage off of the Office 365 reporting, so that you can gain some insights into how your organization is adopting Office 365.

You can find more details here: Explore your Office 365 Adoption Data in Power BI

Power BI – Embedded Accelerator

If you are looking to use Power BI Embedded, Microsoft have a limited offer to help you get up and running.

You can find the blog post here: Power BI Embedded Accelerator

Excel – Get & Transform Updates May 2017

While the features below have been available in Power BI, it is great to see them also incorporated into Excel, as these are some really useful updates.

The first one is the auto selection of the delimiter when using the Split By, I have been using this for a while and whilst it does not appear to be time consuming it is awesome to have it pre-selected for you.

The second one is when combining files to be able to select which sample file to use from a folder.

And finally you can now connect to DB2

You can find more details here: May 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Dynamic Banding across all fields

I had a requirement from the customer where I am consulting and what they wanted was to be able to have dynamic banding. So that no matter what they put into the table it would show the banding where it would not go greater than the banding amount.

This is easiest shown with an image, so if the band was < $1000 the amounts should not be greater than $1000.

So as you can see above in this context the Banding is working as expected, showing values that are < $1000

But when you remove the Document No. I got the following shown below.

Now whilst the SUM Amount is correct, the banding is incorrect, because the SUM Amount is greater than $1000.

So below I show how I got this working.

Creating my Value Banding Disconnected Table

The first thing that I had to do was to create a disconnected table which would hold my banding.

• To do this I used the Enter Data from the Ribbon in Power BI Desktop
  • 
• And I put in the following details
  • 
• I then clicked Ok which loaded it into the Data Model.

Creating measures for my Bands

Next I had to create individual measures for each of the above bands.

• I found that this was the easiest way to ensure that when the data was being filtered by which ever field the user used, it would bring back the correct results.
• I then created my 5 measures as shown below, the reason for using the IF statement is so that if the measure is not within the banding bounds make it BLANK. Which effectively in Power BI will not show any data.

  Band 0 - 100 =
  IF ( [Amount] >= 0 && [Amount] < 100, [Amount], BLANK () )
  
  Band 100 - 1000 =
  IF([Amount] >= 100 && [Amount] < 1000, [Amount], BLANK() )
  
  Band 1000 - 10000 =
  IF([Amount] >= 1000 && [Amount] < 10000, [Amount], BLANK() )
  
  Band 10000 - 100000 =
  IF([Amount] >= 10000 && [Amount] < 100000, [Amount], BLANK() )
  
  Band > 100000 =
  IF([Amount] > 100000 , [Amount], BLANK() )

                          

Final Measure to be used in Table or Visuals

This is the final measure which I can use in a table or visuals. And this uses a combination of the disconnected table and the measures we created above.

• Here is the measure.

  Amount Bands =
  IF (
    HASONEVALUE ( 'Value Banding'[Band] ),
    SWITCH (
      VALUES ( 'Value Banding'[Band] ),
      "< $100", [Band 0 - 100],
      "< $1 000", [Band 100 - 1000],
      "< $10 000", [Band 1000 - 10000],
      "< $100 000", [Band 10000 - 100000],
      "$100 000 +", [Band > 100000]
    ),
    [Band > 100000]
  )

                  

• What the above measure is doing, is looking to see which item has been selected on the Disconnect Table and display the corresponding measure.
• As well as if nothing is selected to default to > $100 000

What it looks like in Action

Below is now what it looks like when I select < $100 and have it using the Document No. (This table is sorted highest to lowest)

And then again if I change it to < $100 000 I see the following for the Document No. (This table is sorted highest to lowest)

Then I did a test where I dragged in a field that I know would aggregate to a higher level, and I wanted to ensure that it would display correctly. And as you can see below, it has put the correct Reason into the correct Bands.

Conclusion

So by creating the dynamic banding I was able to create something for the customer to get some really valuable insights into their data.

BI-NSIGHT – Power BI (Zoom Charts – Preview Power BI Report Server – Featured Data Stories)

For once it has been a quieter week in the world of BI, so here are the updates below.

Power BI – Zoom Charts

I have to say that it has been a while since a visual has really impressed me and the Zoom Visuals do just that.

They provide an almost natural way to interact with your data via the charts.

It is best shown when you go to the blog post and interact with the Power BI Embedded report.

You can find all the details here: Power BI + ZoomCharts = (Power BI)²: Boost your productivity and add the cool-factor to your reports

Power BI – Preview of Report Server

Since the news of Power BI Premium details did emerge that there now would be Power BI Report Server, which you could use as part of Power BI Premium. They have now released a preview version for people to use.

Due to it being a preview version not all the functionality is there, but there are enough options to download, install and start testing it.

You can find the blog post here: Power BI Report Server preview now available

Power BI – Featured Data Stores

There have been some great submissions for Data Stories in the past 2 months. And this is highlighted with the image above. As well as a congratulations to the other people whose stories were also featured.

You can find the blog details here: Congratulations to the Featured Data Stories Gallery submissions for March and April