Press "Enter" to skip to content

Category: Power BI

When To Use SQL, DAX, Or M In Power BI Models

Published 2018-07-02 by Kevin Feasel

Paul Turley offers up some guidance on when to use which language when building Power BI models:

As a general rule of thumb, in formal SSAS projects built on a relational data mart or data warehouse that is managed by the same project team as the BI data model, I typically recommend that every table in the model import data from a corresponding view or UDF stored and managed in the relational database. Keep in mind that is the way we’ve been designing Microsoft BI projects for several years. Performing simple tasks like renaming columns in the SSAS data model designer was slow and cumbersome. Performing this part of the data prep in T-SQL was much easier than in SSDT. With the recent advent of Power Query in SQL Server Data Tools, there is a good argument to be made for managing those transformations but the tool is still new and frankly I’m still testing the water. Again, keep changes in one place for future maintenance.

Do your absolute best to avoid writing complex SQL query logic that cannot be traced back to the sources. Complicated queries can become a black box – and a Pandora’s box if they aren’t documented, annotated and easy to decipher.

But do read Paul’s closing grafs on the importance of not being hidebound.

Comments closed

Using DAX To Calculate Dates Between Transactions

Published 2018-06-27 by Kevin Feasel

Matt Allington needs a haircut:

I got my haircut today (pretty spiffy one too, even if I do say so myself).  While I was chatting I asked my hair dresser “on average, how often should I get my hair cut”?  She told me (for men) around 4-6 weeks.  Then I got thinking (as I do), I wonder if I could data-mine my credit card data using Power BI and find out how often I actually get my own hair cut.  It turns out I was able to do this, and this article explains the hardest part of that task – find the number of days between two transaction dates using DAX.

I’d probably end up doing this in SQL with the LAG function, but it’s good to know several ways to solve date difference problems.

Comments closed

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