Press "Enter" to skip to content

Category: Power BI

Calculating Color Contrast

Published 2020-08-26 by Kevin Feasel

Meagan Longoria has a new Power BI report for us:

Color contrast (as calculated in the WCAG 2.1 success criteria) is dependent on luminance. Luminance is the relative brightness of any point in a color space, normalized to 0 for darkest black and 1 for lightest white. In order to calculate color contrast you must first get the luminance of each color.

As an example, I have colors #F3F2F1 and #007E97. In this hex notation, often explained as #RRGGBB, the first two digits represent red, the second two digits are green, and the last two digits are blue. Each two digits is a value that represents the decimal numbers 0 to 255 in hexadecimal notation. The same red, green, and blue values can be represented in decimal notation as integers, and this is what is used to calculate luminance. #F3F2F1 is RGB(243, 242, 241), and #007E97 is RGB(0,126,151).

Read the whole thing, check out the report, and test those color contrast values.

Comments closed

Displaying Map Routes with Power BI

Published 2020-08-26 by Kevin Feasel

Chris Webb lays out map routes:

In last week’s post I described the new Power Query M functions for working with Well Known Text objects, and in a post a few weeks ago I showed how you can use the Icon Map custom visual to display Well Known Text data. In this post I’ll show you how you can put all this together to do something really useful: display routes on a map in a Power BI report.

First of all you’ll need to download the latest version of the Icon Map custom visual here, because at the time of writing the version in AppSource doesn’t have the WKT functionality needed. Second, you’ll need a data source with a table of latitudes and longitudes that represent points on a route that you want to plot. I’m going to use the Azure Maps Get Route Directions API as my data source; to keep things simple I’m going to use the Shared Key authentication method for this API, but I’ll explain below why this can be a problem in the real world. To follow the example you’ll need to create an Azure Maps Account in the Azure Portal so you can get your own key.

Read on for an interesting demo.

Comments closed

Creating a Power BI Report in Portrait Mode

Published 2020-08-25 by Kevin Feasel

David Eldersveld controls the horizontal; David Eldersveld controls the vertical:

The default Power BI report layout uses a landscape orientation. While it’s not common to see report pages that use portrait mode, Prathy Kamasani (blog/twitter) has a notable example in Power BI here. Others have tried it as well. It’s used occasionally in other data communities or data-driven journalism. Overall, if you want to guide someone through your analysis by having them scroll like a web page, it may be an option to try.

So what happens if I create a report with a vertical layout in Power BI?

Read on for an example as well as some pros and cons of the idea.

Comments closed

Querying Power BI from Visual Studio Code

Published 2020-08-25 by Kevin Feasel

Phil Seamark shows us how to write queries against Power BI using Visual Studio Code:

It’s helpful to understand there are two main client libraries for Analysis Services. A client library is what you can add to any new Visual Studio Code Project to provide objects, methods and functions relevant for the tool you are building.

Make sure you download the NetCore (.Net Core) versions of these libraries when working with Visual Studio Code. There are .Net Framework versions of these libraries that are more suited to use with the full Visual Studio product.

Read on for links to those libraries and a thorough demonstration.

Comments closed

Creating KPIs in Power BI

Published 2020-08-24 by Kevin Feasel

Alberto Ferrari takes us through key performance indicator creation in Power BI Desktop:

Starting from the July 2020 version, Power BI Desktop offers the possibility of using external tools to modify its internal Tabular model. With a tool like Tabular Editor, you can create a KPI directly in Power BI Desktop so that it can be used in any Power BI report and also by using the Analyze in Excel feature. The KPI feature was previously available only in Tabular models created in Analysis Services or Power BI Premium. This introductive article shows you how to create and consume KPIs in Power BI Desktop. A more detailed description of the available KPI graphics and the corresponding state values is the topic for an upcoming article.

Let us see the feature with a practical – though fictitious – example. Say Contoso needs to analyze the Margin % of its products. The yardstick is the overall margin, which is the Margin % over time and products with a tolerance of 2%. The overall margin of Contoso is 53%. Therefore, a category with a Margin % less than 51% is considered bad (red), over 55% is considered good (green), in between 51% and 55% is considered average (yellow). Moreover, Contoso wants to analyze the trend of Margin % compared with the previous year. For example, the margin might be red but Contoso can evaluate which action to take depending on whether it is improving or not over time.

Read on for the demonstration.

Comments closed

Using Perspectives in Power BI

Published 2020-08-20 by Kevin Feasel

Mark Lelijveld walks us through something new in Power BI Desktop’s August 2020 update:

If you work or used to work with Analysis Services, you might know the perspectives functionality. It is a feature inside tabular modelling that allows you to define viewable subsets of a data model.

Each tabular model can include multiple perspectives, where each perspective can include a subset of tables, columns a measures. Especially with large enterprise models, perspectives can be very useful.

With perspectives, you can define specific perspectives to be defines for a specific target audience. For example, the author can create logical subsets of the model for each audience of the dataset. (e.g. Sales, Finance, Marketing, etc.) One thing must clear, perspectives are not object level security or any other kind of security! It is just a better way to view it.

Read on to see how you can create and work with these in Power BI Desktop.

Comments closed

Using Jupyter as an External Tool for Power BI Desktop

Published 2020-08-18 by Kevin Feasel

David Eldersveld continues a series on Power BI external tools:

Many people use Python with notebooks, so let’s take a look at one possible way to enable a Jupyter external tool for Power BI Desktop. The following stepwise approach begins with simply opening Jupyter. It then progresses to creating and opening a notebook that includes Power BI’s server and database arguments. Finally, it works its way toward downloading a notebook definition contained in a GitHub gist and connects to Power BI’s tabular model to start to make this approach more useful.

This post continues a series of posts related to Python and Power BI. The first three parts of this blog series introduced some possible uses for Python connected to a Power BI model, how to setup a basic Python external tool, and how to both use it with a virtual environment and connect to the Tabular Object Model.

This was a cool usage of Power BI’s external tool functionality and starts to give you an idea of how powerful it can be.

Comments closed

Preparing Source Data in Power BI

Published 2020-08-17 by Kevin Feasel

Paul Turley continues a series on doing Power BI the right way:

Your options for transforming data at the source depend on the capabilities of the data source. In general, a query running closer to a data source will be more efficient than one that must cache a set of data and manipulate it downstream. If you are importing text files or Excel files, then your only choice is to import an entire file and then throw away what you don’t need – which is fine for small-ish data sets. On the right side of the previous diagram, some data morphing can be performed in DAX using calculated columns or calculated tables. In rare cases, there may be good reasons but if you can perform row-level calculations in a view or in Power Query, you will be better off to keep all that query logic in the same layer of the solution.

Read on for some good advice, as well as information on query folding, filters, incremental refresh, and much more.

Comments closed

Geometry and Geography Functions in Power BI

Published 2020-08-17 by Kevin Feasel

Chris Webb walks us through some new Power Query functionality:

In the August 2020 release of Power BI Desktop a couple of new Power Query functions were added: Geography.FromWellKnownText, Geography.ToWellKnownText, GeographyPoint.From, Geometry.FromWellKnownText, Geometry.ToWellKnownText and GeometryPoint.From. These functions (which are coming soon to Power Query in Excel too), make it easier to work with geographic and geometric data in the Well Known Text format. You can have all kinds of fun with these functions if you have a visual (like the Icon Map custom visual) that can display Well Known Text data, but I’ll leave that kind of thing for future blog posts. In this post I’ll explain how the basics of how the functions actually work.

So far, it looks like it’s converting strings of latitude and longitude data (in the geography case) into individual elements for plotting, but no distance measures at this time.

Comments closed

Blocking Classic Workspaces in Power BI

Published 2020-08-14 by Kevin Feasel

Adam Saxton points out something new in Power BI:

The ability to BLOCK classic workspaces from being created in Power BI is finally here! Adam shows you how to implement and what to consider. Create Microsoft Teams without the worry!

Click through for a video as well as the Power BI blog post describing this. You can also tell that Adam has the heart of a DBA based on the level of excitement around blocking something. DBAs and goalies, I tell you.

Comments closed