skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


By Avi Singh [Twitter]

Friends at a company play pick-up basketball during their lunch hour. Since there are no established teams, players can be randomly matched up. But these folks happen to be engineers/data-nerds, so they keep detailed track of games, teams, players and win/loss. The diagram view of the data is shown further below.

Question: How can we determine which player pairing is the most successful?
Since players are randomly teamed up, are there combinations which when teamed up have an unusually high winning percentage?


Word Cloud of Player Nicknames: Size of text indicates number of games played

Application: This would naturally extend to other sports, but I believe may also apply in many non-sports scenarios, where items are paired up somewhat randomly (or by design) and we want to know how effective those pairings are.

Thanks to Kirill Perian (basketball nickname K-Real), an attendee of one of our past webinars, who sent us the dataset and posed this question. Dataset has been simplified to showcase this scenario and anonymized to protect the identity of the losers 🙂

File can be downloaded here.


Model Diagram: Showing Game, Team and Team Players

First we will address the easier scenario of creating metrics for individual players, using the many-to-many pattern. Next we will take on writing measures to compare performances of pairs of players.

Resolving Many to Many to create Individual Player Metrics

Let’s start with a really simple metric, count of games played.

GamesCount:=DISTINCTCOUNT(GameTeam[GameID])

This works until we attempt to use a field on the other side of the many to many relationship.


Measure breaks (repeats) when we use a field from Player Table

Between Player and Game, there is a many to many relationship. One Player can play in many Games and one Game would have many Players. The way to map this in Power Pivot is using a bridge table as we have done here using GameTeamPlayer. See model diagram above.

We are not even going to break a sweat solving this one. Been cracked a long time ago, thanks to thinkers like Jeffrey Wang and Gerhard Brueckl. Here is our new measure, see image below for explanation. Read the linked articles, if you really want to dive-in. For most folks I would recommend understanding this as a pattern using image below and simply applying it. (Never heard the “downhill” “uphill” terminology in relationships, for $10 buy the digital version of our PowerPivot DAX book. Would be the best $10 you ever spent).

GamesPlayed:=CALCULATE([GamesCount]), GameTeamPlayer)

You can see, in the Pivot image below,  that the new *magic* measure is working and we have also added some basic additional measures to show games won, lost and win percentage.


New *magic* Measures work perfectly across a Many-to-Many relationship

The perfect Pair of Players

Metrics for individual players were relatively straightforward. But how do we write measures for pair of players? After some head scratching it became clear that I needed another Player table. I left the original Player table alone and added a PlayerA and PlayerB.


PlayerA and PlayerB added with “Inactive” Relationship

I created relationship between GameTeamPlayer and PlayerA/PlayerB, but as you can see by the dotted lines that both relationships are marked as inactive. You can do so from the Manage Relationship window. Why inactive? That will be apparent soon. For now just go with it.


Mark relationship as Inactive

USERELATIONSHIP Warmup

To get us warmed up to the idea of inactive relationship, let us write the GamesPlayed version for the PlayerA and PlayerB tables as below. (Shown for PlayerA; PlayerB works in similar fashion).

PlayerAGamesPlayed :=CALCULATE (
    [GamesPlayed],
    USERELATIONSHIP ( GameTeamPlayer[PlayerID], PlayerA[PlayerID] )
    )

You can see when we put PlayerA on the pivot, while the original GamesPlayed measure does not work (just repeats the same number), PlayerAGamesPlayed shows the correct number. USERELATIONSHIP in effect marks that relationship as Active, during the computation of this measure.


USERELATIONSHIP makes our Measure work!

The Big Kahuna

For the final measure, we are going to repeat the *magic* many-to-many pattern above with some changes

Step A: We still need to refer to the “downhill” table: GameTeamPlayer.

But we need to refer to it in two versions

  GameTeamPlayer Version 1: Filtered using relationship with PlayerA
  GameTeamPlayer Version 2: Filtered using relationship with PlayerB

Step B: Next we need to combine these two version in an “intersect”, so we only count the games that they played together


Intersection of two versions of GameTeamPlayer would lead us to Games Played Together

Here is the formula (lines numbered to aid explanation):-

  1. PlayerABGamesTogether:=CALCULATE( [GamesCount]
  2. , CALCULATETABLE(GameTeamPlayer, USERELATIONSHIP(GameTeamPlayer[PlayerID], PlayerA[PlayerID]))
  3. , CALCULATETABLE(GameTeamPlayer, USERELATIONSHIP(GameTeamPlayer[PlayerID], PlayerB[PlayerID]))
  4. )

Lines 2 & 3 accomplish Step A, giving us two filtered versions of the same table (remember Schrodinger’s Cats/Pivots?). Line 1 has the CALCULATE which accepts the two tables as parameters and combines them to get us the intersection. Note that we use our original measure GamesCount, which to remind you, is defined as below:-

GamesCount:=DISTINCTCOUNT(GameTeam[GameID])

So technically the “intersect” happens at the GameTeam table, where the arithmetic is being performed. That also explains why we left the relationships as inactive. Else, if you think about it, there is really no row on GameTeamPlayer where say Player=”Bad News” and Player is also “Band-Aid”. Don’t make that hurt your head too much. If you feel like you understand the overall pattern and can apply it, that’s all you need. I’ll admit my own understanding ebbs and flows.

And here is the resulting pivot:-


New Measure determines Games Played Together
for all combinations of PlayerA and PlayerB

Let us quickly validate the result. We will do that for players “Melo” and “Mini Lebron” who have relatively few games thus making it easy for us to check the data.

In the image below, we can see the measure PlayerABGamesTogether, and using a detailed pivot can validate that indeed, there were three Game Teams where these two played together.


There were three Game Teams where these players played together

K-Real and Butch – burning up the court

So who is the dominant pair? Looking at data just for K-Real, the pairing of K-Real and Butch stands out. Of all the 164 games K-Real played, they were paired for 51 and have a winning percentage of 73%. Which is markedly higher than K-Real’s other pairings!

Butch happens to be O. Tre. Kirill, let me know if that sounds right? 🙂


K-Real and Butch pairing stands out in terms of Win percentage

The formula used is adapted from the Survey pattern by the Italians (they are the Gods!).
File can be downloaded here.

Power On!
Avi Singh [Twitter]

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 7 Comments
    1. You can bring in multiple copies of the same “Source” table, very commonly done for the Calendar/Date table where you need one Date Table to represent the OrderDate, the next to represent ShipDate, next to represent InvoiceDate etc.

      To bring in a copy of the table, just open “Existing Connections”, select the Connection and click “Open”, now select the table, change the new friendly name if you need to and hit Ok. This way you can get multiple tables in PowerPivot which are all essentially coming from the same source table.

Leave a Comment or Question