Press "Enter" to skip to content

Category: Power BI

Speeding Up Power BI Aggregations With Primary Keys

Published 2018-03-21 by Kevin Feasel

Chris Webb has an interesting use case for adding primary keys on lookup tables:

As you can see, the Property Type column from the #”Price Paid” query contains single letter codes describing the type of property sold in each transaction; the Property Type column from #“Property Types” contains a distinct list of the same codes and acts as a dimension table. Again there’s nothing interesting going on in this query.

The problems start when you try to join data from these two queries using a Merge and then, for each row in #”Property Types”, show the sum of the Price Paid column from #”Price Paid”.

Although baseline performance is bad, Chris shows a way of improving that performance significantly.

Comments closed

Exploratory Analysis With Hockey Data In Power BI

Published 2018-03-20 by Kevin Feasel

Stacia Varga digs into her hockey data set a bit more:

Once I know whether a variable is numerical or categorical, I can compute statistics appropriately. I’ll be delving into additional types of statistics later, but the very first, simplest statistics that I want to review are:

  • Counts for a categorical variable
  • Minimum and maximum values in addition to mean and median for a numerical value

To handle my initial analysis of the categorical variables, I can add new measures to the modelto compute the count using a DAX formula like this, since each row in the games table is unique:

Game Count = countrows(games)

It’s interesting seeing Stacia use Power BI for exploratory analysis.  My personal preference would definitely be to dump the data into R, but there’s more than one way to analyze a data set.

Comments closed

Highlighting Scatter Charts In Power BI

Published 2018-03-19 by Kevin Feasel

Jason Thomas has a great post showing how to implement highlighting of scatter/bubble charts in Power BI:

That said, there is one feature from my previous blog that was not implemented in Power BI – highlighting scatter/bubble charts. In Power BI, the scatter charts are not considered as area charts and hence you can only filter them and not highlight. This feature is useful when you have a lot of data points in your scatter chart and you want to see where a particular data point is with respect to the other data points. That said, you can make use of some nifty DAX and replicate the same behavior.

There are several steps to the process so it’s not point-and-click easy, but Jason has a nice walkthrough showing how to set it up.

Comments closed

Power BI Licensing Costs

Published 2018-03-14 by Kevin Feasel

Jason Thomas has put together a great Power BI report:

I thought it might be useful for some enterprise customers to see what the total cost is going to be for 3 years, and decided to share it here. You can use this guide to see some of the additional information like:-

  1. Forecast the growth in % for Pro, Frequent and Occasional users
  2. Get the total cost for 3 years based on the growth
  3. See the per user cost for each year
  4. Also, see the estimated utilization of the last Premium node, which will give you a good idea on whether you are close to upgrading or not

This is rather useful for long-term planning.

Comments closed

Accessibility And Power BI Reports

Published 2018-03-12 by Kevin Feasel

Meagan Longoria has some tips to make your Power BI reports easier for people to read:

Avoid using color as the only means of conveying information. Add text cues where possible. It’s very common to show KPIs with a background color or a box next to a metric that uses red/yellow/green to indicate status. Users who have difficulties seeing color need another way to understand the status of a key metric. This could mean that you use a text icon in addition to or instead of color to indicate a status. Power BI reports often include conditional formatting to change the background color or font color of items in a table to convey high/low or acceptable/unacceptable values. If that is important for your users to understand, you could add a field containing the values “high” and “low” to the table itself or to the tooltips. Tooltips are accessible to screen readers via the accessible Show Data table (Alt + Shift + F11).

These are good design principles in addition to providing accessibility benefits.

Comments closed

Managing Multiple Power BI Accounts With Chrome

Published 2018-03-12 by Kevin Feasel

Ike Ellis has a quick tip for managing multiple Power BI accounts across different clients:

 As a consultant, I find it difficult to switch between accounts on PowerBI.com.

I have to log out of an existing account and log back in to a new account. The login process takes a long time. I have found a work around. I use google chrome to manage different chrome accounts, different themes, different cookies, and this allows me to stay logged in to multiple power bi accounts at the same time.

Great tip.

Comments closed

Invalid Dates And Power BI

Published 2018-03-09 by Kevin Feasel

Melissa Coates notes a discrepancy between the Desktop and Service versions of Power BI:

Last week I got involved with a customer issue. A refresh of the data imported to a PBIX always works in Power BI Desktop, but the refresh operation intermittently fails in the Power BI Service. Their workaround had been to refresh the PBIX in Desktop and re-upload the file to the Service. This post is about finding and fixing the root cause of the issue – this is as of March 2018, so this behavior may very well change in the future.

Turns out, the source of the problem was that the customer’s Open Orders table can contain invalid dates – not all rows, just some rows. Since Open Orders data can fluctuate, that explains why it presented as an intermittent refresh issue. Here’s a simple mockup that shows one row which contains an invalid date:

At this point, we have two open questions:
(1) What is causing the refresh error?
(2) Why is the refresh behavior different in the Service than the Desktop tool?

Read on for the explanation of the difference, as well as a fix to prevent refresh errors due to invalid dates.

Comments closed

Explaning RANKX In DAX

Published 2018-03-06 by Kevin Feasel

Philip Seamark explains how the RANKX function works:

One of the first traps to encounter when using this function is the function can be used in calculations for calculated columns as well as calculated measures.   The RANKX function will still do what it is asked.  The trick is how you use the function in each scenario – and more importantly, what filters are going to be implicitly applied to the data the RANKX function actually uses.

This is a helpful post for explaining the ranking function.

Comments closed

Viewing N Months In Power BI

Published 2018-03-05 by Kevin Feasel

Jason Thomas shows a nice method to combine data for the last N months along with a month-by-month breakdown using a single date dimension:

Create 3 measures as shown below, and then add those 3 measures in the report along with a month slicer as shown below. You can change the month in the slicer and verify that the measure values change for the selected month.


Sales (Selected Month) = SUM ( Sales[Sales] )

Sales Last Year = CALCULATE ( SUM ( Sales[Sales] ), SAMEPERIODLASTYEAR ( ‘Date'[Date] ) )

Sales YTD = TOTALYTD ( SUM ( Sales[Sales] ), ‘Date'[Date] )

This is the first time I’ve seen a What If parameter in use.  Very interesting.

Comments closed

Using Power BI For Hockey Stats

Published 2018-03-02 by Kevin Feasel

Stacia Varga continues her Power BI + hockey series:

My last data acquisition step is to get statistics data for each player. I just need to build a function to dynamically get data by team like I did above using this endpoint as my base structure:

http://statsapi.web.nhl.com/api/v1/teams/54?hydrate=roster(person(stats(splits=yearByYear)))

It turns out there are many different kinds of statistics that I can get in addition to these statistics by season. I’ll probably get them all added into my model eventually, but the process is the same. For a list of other available statistics to use instead of yearByYear, see http://statsapi.web.nhl.com/api/v1/statTypes.

It’s another nice use of Power BI to read from a web-based API.

Comments closed