Press "Enter" to skip to content

Category: Power BI

Checking that Power BI Security Roles are Correct

Published 2020-08-28 by Kevin Feasel

Fred Kaffenberger poses a question:

If you can ask, how do we know that we are improving, you should also be able to ask how do we know that the security roles are implemented correctly. Data culture is not just for the business, but for the reporting team as well. I haven’t seen much discussion of auditing security roles in Power BI circles, so I’m genuinely curious about how others tackle this issue. Does everyone simply work hard and hope for the best? Or do you restrict everything at the database level and use different apps for different groups instead? There may even be regulatory reasons which require you to restrict it at the database level. But even if you do restrict everything at the database level, you still need to validate that security as well.

Read on for a verification technique.

Comments closed

Passing Power Query Parameters to Stored Procedures

Published 2020-08-28 by Kevin Feasel

Soheil Bakhshi shows how we can take an input from Power Query and pass it to a stored porcedure:

This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.

The solution is somewhat easy.

If you’re familiar with SQL Server Reporting Services, the solution instantly makes sense.

Comments closed

Using Calculation Groups for Dynamic Measure Formatting

Published 2020-08-27 by Kevin Feasel

Matt Allington wants to solve a problem:

Using a switch measure to toggle results is a mature and common technique used in Power BI and Power Pivot for Excel. For example, a switch measure can be used to toggle what appears on a chart so that the end user can easily switch the data being visualised (see image below).

This technique uses a disconnected table, a slicer to receive the user selection, and a switch measure to change the result of the measure based on user input. When you select an option on the slicer you can have your chart update to show the result you want to see in the chart. I blogged about this in 2014 here. 

The switch measure has a drawback, though: you can only have one number format, so Matt has a new solution using calculation groups.

Comments closed

Dynamic Date Column Headers in Power BI

Published 2020-08-27 by Kevin Feasel

Gilbert Quevauvilliers shows how we can display the last several days as column headers:

I find I do love a good challenge and I find that I get them frequently. I also enjoy sharing what I have learnt with others.

In this blog post I am going to show you how I found a way to create Dynamic Date Column Headers for Tables or Matrixes in Power BI.

Below is what it looks like once completed. What I did was to create a matrix with the last 5 Days (Showing each date for the last 5 days) and then along with this an MTD calculation which shows not only the header of MTD but it includes the current months values.

This is an interesting one for sure. Read on to see how Gilbert solved the problem.

Comments closed

Power BI Fonts and Dyslexia

Published 2020-08-26 by Kevin Feasel

David Eldersveld has some tips to make it easier to read your dashboards:

Power BI font selection is currently limited to about two dozen choices. These include a mix of serif versus sans serif, monospaced versus not monospaced, bold versus light, and more. While I do not have a definitive list of specific fonts–and I would love additional input from readers who live with dyslexia–there are some preferred font choices as well as choices to avoid when designing Power BI reports.

Click through for good recommendations regardless of whether any of your dashboard viewers are dyslexic.

Comments closed

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