Happy Birthday Power BI!

Today marks one years since Power BI was officially released.  It’s been an amazing ride and I’d like to thank the team at Microsoft for so responsive to the community’s feedback.  Check out this video the community created to celebrate! 

Power BI Custom Visuals Class (Module 08 – Dot Plot)

Dot Plot

In this module you will learn how to use the Dot Plot Power BI Custom Visual.  The Dot Plot is often used when visualizing a distribution of values or a count of an occurrence across different categorical data you may have.  Watch this module to learn more!

Module 08 – Dot Plot

Downloads

Key Takeaways

  • The Dot Plot is a simple visual that shows a representative number of dots for the value you wish to display.

image

The dot chart color can easily be changed under the Format settings.

  • Under the Format paintbrush there are only a few relevant properties worth noting for the Dot Plot under the X-Axis, Data labels, and Data color sections.
  • The X-Axis section gives you the ability to turn off the title on the chart’s x-axis.  You can also change the color of the category labels here, which I recommend making a more bold color than the default color.

image

  • The Data colors property allow you to adjust the color that’s used throughout the chart.  I would love to see in the future if this could be split into multiple colors based on different series on the chart.  For the time being it’s a simple property for setting the color for the entire chart.

image

  • Finally, in the Data labels property you can change the following on the data labels that appear above each chart value:
    • Text Size
    • Decimal Places
    • Display Units (None, Thousands, Millions, Billions, Trillions)
    • Color

image

  • In addition to these properties you have a set of settings that appears on every visual to adjust the background color, add a border around the visual and lock the aspect ratio.

Find Out More

You will always be able to find this video module and advanced viewing of future modules on the Pragmatic Works On Demand Training platform.  Click here to learn more about this training platform that includes 20+ courses.

Power BI Custom Visuals Class (Module 07 – Enhanced Scatter)

Enhanced Scatter

In this module you will learn how to use the Enhanced Scatter Power BI Custom Visual.  This new-and-improved scatter chart allows for more customization and better data representation over the standard scatter chart that is provided to you by default with Power BI.

Module 07 – Enhanced Scatter

Downloads

Key Takeaways

  • The Enhanced Scatter functions very similarly to the standard Power BI scatter chart but with a few new properties added to it including:
    • Shapes as markers
    • Background image support
    • Crosshair interaction

image

Many of the new properties can be data driven, including the shape of the indicator which can be pulled in from your dataset.

image

Data marker colors can be set dynamically from your data with a simple calculation and a background image can be set on the chart.

  • Under the Format paintbrush there are many options you can use to customize the Enhanced Scatter.
  • The Data colors section gives you the ability to manually change the colors of each of your data categories presented on the chart.  However, this would ideally be data driven.

image

  • The X-Axis and Y-Axis properties allow you to adjust the start and end points of the chart.  If you don’t set these Power BI will estimate when the chart should end.
    • You can also adjust the Display Units to show in Thousands, Millions, Billions, Trillions or no formatting at all.
    • Lastly, you can turn on or off showing the title of the axis. This is on my default but might make sense to turn off if the values are fairly obvious what they mean and you are trying to recover some more space on the report design surface.

imageimage

  • Turning on the Category labels allows you to place a label above each indicator, similar to the screenshot of the gas station above.

image

  • If you would like to setup a background image on the chart then use the Backdrop property and provide a URL for the image you would like to use.  This currently only supports Image URLs.  The standard Power BI scatter chart also supports have an background image imported from a file.

image

  • Turning On the Crosshair property allows you to hover above the chart and know which data point you’re looking at as shown below.

image

  • Using the Outline property simply places a thin outline around the markers on the chart.  It’s barely noticeable but may help set values apart, especially if all your markers are the same color.

imageimage

  • In addition to these properties you have a set of settings that appears on every visual to adjust the background color, add a border around the visual and lock the aspect ratio.

Find Out More

You will always be able to find this video module and advanced viewing of future modules on the Pragmatic Works On Demand Training platform.  Click here to learn more about this training platform that includes 20+ courses.

Power BI Custom Visuals Class (Module 06 – Histogram)

Power BI Module 06 - Histogram

In this module you will learn how to use the Histogram, a Power BI Custom Visual.  A Histogram is a column chart which shows the distribution of occurrences divided into categories, called bins.  This type of chart is useful for estimating density and discovering outliers.

Module 06 – Histogram

Downloads

Key Takeaways

  • The Histogram automatically performs a discretization process to create bins for continuous values.
  • You do have the option to manually overwrite the number of bins that are automatically generated for you.
  • This chart type does support cross filtering with other visuals you may use on the report.

image

The number of bins can be adjusted. In this case, increasing the bin size exposed a set of outliers in the data found in the last bin.

  • Under the Format paintbrush the options that are particularly interesting for the Histogram can be found under the Data labels, Data colors and General setting menus.
  • There’s not much in the Data labels properties but you can adjust the number of decimal places used on the labs.
    • Typing a value of 2 here will display 2 decimal points on your data labels. 0 or blank would show no decimal places.

image

  • Under the Data colors properties you have a pretty self-explanatory  option to change the color of the Histogram bars to your preferred color.

image

  • In the General properties section you have typical option to control the size and location of the visual but there are also settings here to control Histogram specific features.
    • By turning off Frequency (On is the default) it will change the chart to show a scale based on density instead of frequency.
    • The Bins option allows you to overwrite the default discretized bins that are created tell the Histogram a specific number of bins you would like.  Changing this value can sometimes increase the chance of noticing an outlier in the data.

image

  • In addition to these properties you have a set of settings that appears on every visual to adjust the background color, add a border around the visual and lock the aspect ratio.

Find Out More

You will always be able to find this video module and advanced viewing of future modules on the Pragmatic Works On Demand Training platform.  Click here to learn more about this training platform that includes 20+ courses.

Power BI – Checking Query Folding With View Native Query

View Native Query

A really great feature that was silently added into June update of the Power BI Desktop is a feature called View Native Query.  This feature is integrated into Power BI Query Editor and appears to give you the ability to see the queries that are running against your data source when Query Folding is taking place.  I’ll get back to what View Native Query does in a moment, but first for those that are unfamiliar let’s talk a bit about what Query Folding is and why it’s important.

Query Folding

The idea behind Query Folding is to push the logic that you built into a Power BI query back to the data source server and execute it there in it’s native language instead of doing a client side transform of the data.  Why is this important?  Let me give you an example.  Say you have a 2 billion row SQL Server table you need to connect to in Power BI, but you want to filter to only return the last year of data.  With Query Folding the filter of that data is done on the SQL Server side instead of on the client side. If Query Folding did not take place then that would mean all 2 billion rows would be brought across the network only to then filtered out on the client workstation.  So clearly the ideal situation is that all your queries get folded for the best possible performance, but Query Folding only works in certain scenarios.

Here’s the scenarios where Query Folding does not take place:

  • You are using an unsupported data source. This usually makes sense. For example, if you’re connecting to a flat file that there is no backend server that the queries can be run against.
  • You are using an unsupported transform type.  Generally this makes sense too.  For example, if you’re connecting to a SQL Server data source and you select a tranform in the Query Editor that doesn’t exist in SQL Server.  Maybe you choose the transform Capitalize Each Word.  Now there are ways to accomplish this in SQL Server but there is not a native T-SQL function that can capitalize the first letter of every word in a field.
  • You write your own source query. When you establish a connection in Power BI to a database (let’s say SQL Server for this discussion) you’re provided an option, under Advanced Options, to write your own SQL Statement to define how the data will be imported.  Once you choose this option Query Folding is no longer available on this query. That means if you plan on writing a query to import your data you better know ever transform you would like to apply to the dataset and write it inside the SQL Statement because if you add any additional transforms in the Query Editor later they will not be folded.

image

The good news is Query Folding is not all or nothing.  You can have partial folding that takes place. The order of your tranformations can make a difference in how much is folded back to the server. For example, a Group By transform should appear before a transform like Capitalize Each Word because Group By would be supported on the source system while capitalize each word would not be. So the Group By statement would be pushed back to the server to perform that work while the Capitalize Each Word is done on the client machine.  If the Capitalize Each Word transform came first then everything after it would not be folded no matter what type of transform it was.

View Native Query to the Rescue

In the past it’s been fairly difficult to determine if your query was truly folded or not.  One option was if your data source was SQL Server you could use a tool like SQL Server Profiler to view a trace of all the queries running against your server.  While this helped it only worked for SQL Server data source and maybe more importantly it’s hardly a tool that the typical Power BI user would know.

Now with the latest Power BI Desktop update you have a new option called View Native Query, which appears to follow the rules stated earlier about when Query Folding can and cannot take place.  To use it simply use the Query Editor like you normally would and then right-click on a step in the Applied Steps section.  Assuming you have followed the rules of Query Folding then you will see an option called View Native Query.

image

When you select this option a Native Query window will appear that shows you the exact query that is being run against your data source.  In this example I connected to a SQL Server database then connected to the Product table and applied a simple filter of the data using the Query Editor transforms.

image

Notice this Query Editor filter is converted to a native SQL statement and run against the server instead of my client machine.

Now, if I introduce a new transform like Capitalize Each Word, which we know Query Folding doesn’t support, notice that the option to View Native Query is disabled.

image

This also means any transform that we add after this one will not be folded and the View Native Query option will continue to be disabled.  However, don’t forget that queries can be partially folded, which means any transform that was applied prior to this unsupported function will still be folded and will allow you to use the View Native Query option.

Below shows when you right-click on the previous step that the View Native Query is supported up until Capitalize Each Word is used.

image

I’m really excited about this new feature because it will be a huge help in troubleshooting performance problems and I hope this was helpful to you!

Power BI Row Level Security

Power BI Row Level Security

In the June update of the Power BI Desktop there were some really cool features that were added.  I’d encourage you to review the full Power BI blog to see all the June updates.  My favorite of all these new features is Row Level Security (RLS) for Power BI.  The Power BI team blog didn’t go into any great detail yet (I’d expect a follow up blog from them later) so I thought I may give you a little more context here.

Power BI uses a role based security model, which means you are defining a role and then assigning users to that role.  Within that role you can restrict users from seeing all rows within a table or just specific rows.  For example, if I want my Southeast sales team to see only their data then I would create a role that uses a DAX filter on the table that defines the sales regions.  This will also filter any other table in your model to only return Southeast sales, so long as your data model has relationships defined between these tables.  Check out this video to see how it works.

Row Level Security

Step by Step

  • To create on a Power BI Solution you will start by going to the Modeling ribbon and then select Manage Roles.

SNAGHTML6b4f40

  • This will launch the Manage roles window where you will select Create to add a new role.
  • After you give the role a new name you can begin assigning DAX filter expressions to it.
  • Click the ellipsis next to the table you would like to apply the filter to and then select Add filter.
  • If you select Hide all rows then all rows for this table will be hidden (as the name implies) but if you choose a field you can apply a filter to specific values in the table.

image

  • Once you are happy with your selection hit Save.
  • To test the security model go back to the Modeling ribbon and select View As Roles.
  • Here you can select the role you want to test and then click OK.

image

  • This will filter the result to only show your selected roles filters.
  • To stop impersonating this role you can click Stop Viewing to return to seeing the results without this filter applied.

SNAGHTML749640

  • The next step is to assign users to the roles, which must be done from the Power BI Service
  • That means you must deploy your model to the Power BI Service first. To do that go to the Home ribbon and select Publish.
  • Once you’ve published to the service login to Power.com and click on the ellipsis next to the dataset you just deployed.  In this menu you will select Security.

image

  • Next, select the role you created and enter the email addresses of the users you would like to have the role assigned to. Click Add after you’ve entered all the users.

image

  • Once you click Save on the bottom your security is ready to go!
  • To test it out click the ellipsis next to the role name and select Test as role.

image

  • This will allow you to now view reports while impersonating this newly created role.

image

I hope you like this new features as much as I do. To learn more about Power BI I recommend viewing my Pragmatic Works On Demand Training course called Power BI Desktop and Dashboards.

Power BI Custom Visuals Class (Module 05 – KPI Indicator)

Power BI Module 05 - KPI Indicator

In this module you will learn how to use the KPI Indicator (This is kind of like saying “ATM Machine” isn’t it?), a Power BI Custom Visual. Power BI comes standard with a KPI visual but the custom visual we will cover in this post shows a few more options than is available in the standard tool. Using the KPI Indicator allows you to not only visualize key performance indicators but also include a historical trend line or bar chart with it.

Module 05 – KPI Indicator

Downloads

Key Takeaways

  • The KPI Indicator allows for comparison of actual and target values.
  • The deviation between these two values is shown as a percentage.
  • You can optionally choose if you would like to visualize a historical trend line or bar chart

image

The chart type can easily be change between line and bar chart.

  • Under the Format paintbrush you only one property section that is unique to the KPI Indicator. The others come standard with every visual.
  • In the KPI properties you can do the following:
    • Change the name that appears on the KPI Indicator
    • The Banding percentage allows you to adjust the threshold of when something is set as red, yellow or green.  If the Banding comparison property is set to “Relative” this value should be entered as a percentage number (of the target). If the Banding comparison property is set to “Absolute” this value should be entered as a non-percentage number (that will be added/subtracted from the target instead).
    • Change the Banding type property to one of the following:
      • Increasing is better –  Increasing is best when you’re measuring things like sales or profit. If you go over your profit target that’s a good thing!
      • Decreasing is better – Decreasing is probably best when you’re looking at something like budgeting. Staying under budget is usually a good thing. Unless you being too far under budget means you won’t get that money again next year which leads to the last option
      • Closer is better – This is for when you need your data to land in the middle of a bell curve.  Meaning if you go too high or too low that’s a bad thing. This is often useful when looking at medical data.  For example, if your blood pressure is too high then that’s a bad thing, but if you’re blood pressure is too low that’s also a bad thing too. You need to land in the middle somewhere, which is what this option allows.
    • The Banding comparison can either be “Relative” or “Absolute”. If this property is set to “Relative” the banding percentage is calculated as a percentage of the target. If this property is set to “Absolute” the banding percentage is calculated as a subtraction to/from the target.
    • The Chart type property decides whether the trend indication should be a line chart or a bar chart.
    • The Deviation as % is On by default. This means that the Actual value compared to the Target value will be presented as a percentage deviation. The deviation is presented to the right of the actual value. If this property is switched off the deviation will not be treated as percentage. It is typically useful when Banding comparison is set to “Absolute” and the actual value is not measured in %.
    • The Thousand separator property can be used to force the formatting of the actual value to be formatted with thousands separators. This is typically useful when working with larger non-percentage measures.

image

Many of the property descriptions above were obtained from Fredrik Hedenstörm, the designer of the visual.

  • In addition to these properties you have a set of settings that appears on every visual to adjust the background color, add a border around the visual and lock the aspect ratio.

Find Out More

You will always be able to find this video module and advanced viewing of future modules on the Pragmatic Works On Demand Training platform.  Click here to learn more about this training platform that includes 20+ courses.

Follow

Get every new post delivered to your Inbox.

Join 56 other followers