skip to Main Content

A quick update here on the football project (not really warranting a video).  The source db is awfully complex, and I’m in the process of wrapping my Excel-centric head around it.

For instance, in the Plays table, I’m trying to understand how many row constitute a single NFL play.  It’s not one row per play – no, that would be too simple.

Here, for instance, is a field that claims to be a unique play ID, but when I sort by it, it clearly isn’t:

                       PowerPivot PlayID Sorted  

With the table sorted, though, I notice that there are multiple SeasonNumber values corresponding to PlayUniqueID = 1:

                       PowerPivot Season Number 

…so I add a calc column concatenating the two, with renewed hopes for uniqueness:

PowerPivot New UniqueID

And…  curses, no such luck:

                     PowerPivot PlayGUID

At this point I realize that there really just are multiple rows per real event.  There are other columns in there that I suspect are crucial:  PlayerRole, PrimaryRole, etc. – all with integer values, sadly.

The good news, though, is that those integers are ID’s, and they reference into other tables that I are related to the Plays table.  Once those relationships are set up, I can exploit a new function added by PowerPivot.

To gain some more understanding about how this table works, I decide to lean on a familiar Excel trick and just add some more columns, VLOOKUP-style, but using the new DAX function named =RELATED()

PowerPivot =RELATED 1

            PowerPivot =RELATED 2

                           PowerPivot =RELATED 3

Be gone, VLOOKUP!

Check that out – it’s a single-parameter function!  I just say, “hey, go get me this column from this related table,” and off it goes to fetch the values that match the row from the current table, using the relationship.

The equivalent VLOOKUP would be something like:

    =VLOOKUP([PlayerID, CleanPlayers, 2, FALSE)

…and if the PlayerID column wasn’t the first column in the CleanPlayers table, well, it flat-out wouldn’t work.

Those three =RELATED() formulas yield:

PowerPivot =RELATED results

Ok, this gives me some hope of figuring things out.  (Steve Martin’s presence confuses me, though…)

I may or may not keep these columns in this table when I am done, since they are NOT required – I can build reports using the original fields in those other three tables, combined with fields from the Plays table, as demonstrated in previous posts.  This is just a temporary convenience for exploring this table.

Note that Excel users work like this all the time.  I kinda doubt DBA’s frequently add columns like this, expecting to just remove them shortly thereafter.  But here I am, working with an environment that conveys real database advantages, but I’m still doing my informal, explore-and-experiment Excel shtick.  I love it.

Next Football Post:  The Greatest Function in all of PowerPivot >>

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 14 Comments
  1. Hi Rob – just finished working through the football project and I’m pretty sure that will be the best into to PowerPivot that I ever see – thanks so much for taking the time to start shifting those ideas into the general business population. I am a financial analyst, not an IT person and have had no exposure to SSAS. Even so, I was able to follow through your football project and immediately see how PowerPivot is going to change the way that my colleagues and I attack problems. PP is going to deliver us from sheets upon sheets and lookups upon lookups and the attendant molasses Excel performance. For that you, mate, are a ‘king hero!
    I need PP yesterday.
    cheers, Duncan

    1. Why thank you Duncan, that is very gratifying to hear 🙂

      You might want to check out the Great Football Project category as well, because I got kinda lazy at some point and stopped linking from one post to the next 🙂

      -rob

  2. Why does my related function return an error even though I have relationships between two tables using a column with unique values?

  3. I would like to know a function that is similar to HLOOKUP.

    I can’t to do the HLOOKUP with the RELATED function….

    Someone knows how to do?

  4. Hi Rob! First of all I would like to thank for this amazing blog and to share your knowledge with us. Second, sorry my english because I’m beginner it. So, let’s go to my doubt. I have two tables on the PowerPivot where are linked for field “Index” in both. This field as a result of others fields concatenated to the finality to create unique values for use the DAX Related() Function, however not find all the values, just a few. I used the Countif function in the Excell (2016) to confirm if there are repeated values, but there aren’t.

    When I use the DAX Lookupvalue() function, return the same results.

    Could you help me please?

  5. Rob, I realize this an old post, but it was the most relevant to my situation.

    I’ve bought and read (mostly) your great book Power Pivot and Power BI (2nd Edition) and you discuss using the “Related” function in there as you described above. However, when I read your text, it definitely sounded like this was not good programming style and you suggested that you erase them when done with development.

    I have many situations where my “data” tables will have one or more numerical fields, but I would like to convert those to other values through the use of other numerical factors in a “lookup” table (i.e. think currency conversions where each record in the data table could have a different country).

    I can do this with the Related function through a Calculated Column, but if this is the not the best method, what is the best way? I’ve tried the use of SUM or SUMX in a Calculated Column or as a Measure and gotten some combinations that actually “worked”, but not sure if it’s giving me what I am expecting.

    Any suggestions? Thanks.

Leave a Comment or Question