Press "Enter" to skip to content

Category: Power BI

Power BI Helper Version 2.0 Released

Published 2018-06-21 by Kevin Feasel

Reza Rad has a new version of Power BI Helper:

I started Power BI Helper with the intention to help to find issues in Power BI reports faster and easier. This tool over time became better and better. I’m excited now to let you know that the version 2.0 of this product is now available for everyone to use and enjoy. This version comes with these features:

  • Connecting to more than one Power BI model. Selection option for the model.

  • Showing the connection mode of the Power BI file.

  • Showing list of tables that are NOT used in any visualization, and can be hidden from the report.

  • Modeling advises

    • List of both directional relationships
    • List of inactive relationships

  • Some minor bug fixes

It looks like quite the useful tool.

Comments closed

Flattening JSON In Power BI

Published 2018-06-20 by Kevin Feasel

Imke Feldmann shows how to flatten JSON data imported into Power BI:

If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what’s in there. The following function has you covered for this task. It returns a table with all values in column “Value” and additional columns describing where that value came from in a hierarchical form, no matter how deep the nesting goes:

Click through for a script which shows that the process is a lot more complicated than I had expected.

Comments closed

Auto-Indentation In Power BI’s DAX Formula Bar

Published 2018-06-20 by Kevin Feasel

Chris Webb shows an easy-to-miss feature in Power BI:

The other day I discovered something new (at least to me) while writing the DAX for a measure in Power BI Desktop: when you insert a new line in your DAX expression using SHIFT-ENTER it also auto-indents the code. I asked a few people if this was new because I was sure I hadn’t seen it before, even though I always put line breaks in my code; of course Marco had and said he thought it had been around for a while. Anyway, Marco then commented that most people didn’t know you could even put line breaks in DAX and I thought to myself I should probably write a short blog post about all this, because of course line breaks and indentation make your code much more readable.

Click through for a demo as well as a couple of tips around this.

Comments closed

Optimizing Conditionals In DAX

Published 2018-06-19 by Kevin Feasel

Marco Russo shows us a way to optimize mutually exclusive conditional calculations using DAX:

In previous articles, we discussed the importance of variables and how to optimize IF functions to reduce multiple evaluations of the same expression or measure. However, there are scenarios where the calculations executed in different branches of the same expression seem impossible to optimize. For example, consider the following pattern:

1
2
3
4
5
6
Amount :=
IF (
    <condition>,
    [Credit],
    [Debit]
)

In cases like this involving measures A and B, there does not seem to be any possible optimizations. However, by considering the nature of the two measures A and B, they might be different evaluations of the same base measure in different filter contexts.

Read on for a couple of examples.

Comments closed

Disambiguating “App” In Power BI

Published 2018-06-18 by Kevin Feasel

Melissa Coates gives us the different forms of what an “app” is in the Power BI world:

Let’s say you just heard someone mention a Power BI app. What exactly do they mean by that? Well, it depends. The term “app” is used kind of a lot in the Power BI world. So, here’s a quick reference to help you decode the conversation. I’m going to start with the most likely options, working down to other options. Which one someone is referring to really depends on their role and their level of familiarity with the Power BI ecosystem.

Power BI App

A Power BI App is a packaged up set of content in the web-based Power BI Service. Related reports, workbooks, dashboards, and datasets are published from an App Workspace into an App for users to consume.

Power BI App Workspace

An App Workspace in the Power BI Service is where reports, workbooks, dashboards, and datasets are saved, and where data refresh schedules and other settings are defined. An App Workspace is suited to development & collaboration with coworkers (whereas My Workspace is a private area). Smaller teams might do everything they need to do within an App Workspace, whereas larger teams use an App Workspace as the collaboration area for content before it gets published to a Power BI App for consumption. You can have quite a few App Workspaces, depending on how you organize content (for instance, by subject area, by project, by department, or by type of analysis).

Click through for several other potential answers for what that user means by “app.”

Comments closed

Playing Blackjack With Power BI

Published 2018-06-12 by Kevin Feasel

Phillip Seamark goes and creates a blackjack game in Power BI:

The last of the three data-tables in the model used to control Player 1 is the ‘P1 Turn’ data-table.  This is simply a 10-row single column table with numbers 1 through 10.  The purpose of this table is to use in conjunction with a slicer that will help keep track of what turn Player 1 is up to.  A series of 5 bookmarks will be used to snapshot a slicer selected in 5 different states.  A series of bookmarks will be taken with this slicer having a different value selected which is how the game can keep track of the progress through the game.

A [P1 Card Filter] calculated measure keeps track of the selected value over the above slicer which is used as a filter on the table-visual that is used to reveal cards for Player 1.

It’s more a toy solution than an actual game, but it’s interesting to see.

Comments closed

Making Power BI Reports Accessible

Published 2018-06-07 by Kevin Feasel

Meagan Longoria has a checklist for ensuring that your Power BI reports are accessible to all users:

Tooltips

  • Don’t use tooltips to convey important information. Users with motor issues and users who do not use a mouse will have difficulties accessing them.

  • Do add tooltips to charts as ancillary information. It is included in the accessible Show Data table for each visual.

There’s a lot of good information here.

Comments closed

Securing Power BI Report Server

Published 2018-06-01 by Kevin Feasel

Steve Hughes gives some advice for securing a Power BI Report Server installation:

You have essentially three layers of access to the report file security in Power BI Report Server.

  1. The portal itself can be secured. You can and should limit access to the reports by only allowing specific users or groups access to the report portal.
  2. Folders can be used to provide more granular security over a group of assets in the report portal. In the image above, I created a folder called PBI Secure Reports. A specific AD group has access to this folder. If a user does not have permissions to the folder, the folder does not show up in the portal and they cannot access the folder or the assets, including Power BI reports, stored in this folder.
  3. Individual reports can be secured as well. I never recommend this option as it becomes administratively difficult to manage. However, the capability is there is a single asset needs to be secured in this fashion.

These options work for any asset stored in the Report Portal and are not limited to Power BI reports.

Power BI Report Server is a different animal from standard Power BI, so securing it will be a bit different as well.

Comments closed

Power BI Color Palattes

Published 2018-05-29 by Kevin Feasel

Meagan Longoria helps us choose a color palette for Power BI reports:

A color palette is simply a collection of colors applied to the visual elements in your report. What we typically refer to as color is a combination of three main properties: hue (base color on the color wheel), intensity (brightness or gray-ness) and value (lightness or darkness). You can build an engaging and professional looking report with just 6 colors. It’s possible to have fewer colors or more colors, but 6 should cover many common visualization needs. If you are using more than 6 colors, you might want to check that you are optimizing engagement and cognitive load.

  1. Main color – default color on graphs

  2. Color 2 – used when multiple colors are needed in a graph or report

  3. Color 3 – used when multiple colors are needed in a graph or report and Color 2 has already been used

  4. Highlight color – a color used to highlight important data points to make them stand out from other points on the page

  5. Border color – a light color used for borders on tables and KPIs where necessary

  6. Title color – color used for visual titles and axis labels as appropriate

There’s a lot of good advice in here.

Comments closed

Joining Tables In DAX

Published 2018-05-22 by Kevin Feasel

Marco Russo bridges a gap in moving from SQL to DAX:

The SQL language offers the following types of JOIN:

  • INNER JOIN
  • OUTER JOIN
  • CROSS JOIN

The result of a JOIN does not depends on the presence of a relationship in the data model. You can use any column of a table in a JOIN condition.

In DAX there are two ways you can obtain a JOIN behavior. First, you can leverage existing relationships in the data model in order to query data included in different tables, just as you wrote the corresponding JOIN conditions in the DAX query. Second, you can write DAX expressions producing a result equivalent to certain types of JOIN. In any case, not all the JOIN operations available in SQL are supported in DAX.

Read on for several examples.

Comments closed