Power BI Custom Visuals Class (Module 09 – Chord)

Chord

In this module you will learn how to use the Chord Power BI Custom Visual.  Chord diagrams show directed relationships among a group of entities using colored lines (chords); this allows for an easy representation of correlating data.

Module 09 – Chord

Downloads

Key Takeaways

  • A chord diagram is a graphical method of displaying the inter-relationships between each data category
  • A data category can have a chord that connects to other categories or back to itself
    • When a chord connects to another category it means that values have changed from one category to another. 
    • The color of the chord indicates which category was more dominate.
    • When a chord connects back to itself it looks like a camel hump and shows that there was no change

image

This shows that a lot of Samsung users remain Samsung customers, but it also shows that Apple is taking a portion of their market share.

image

Selecting a data category will also cross filter any other visualizations in the report.

  • Under the Format paintbrush there are some options that are helpful with customizing the appearance of the Chord chart.
  • The Data colors section gives you the ability to change the Default color values on the chart.  You can also turn on Show all and provide specific colors you would like to see on each category of the Chord.

image

  • The Axis property is a simple one. This allows you turn on/off the data labels on the Chord Axis.

image

  • Next, in the Labels section you can define the data category labels Color and Text size.

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.

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.

Follow

Get every new post delivered to your Inbox.

Join 56 other followers