skip to Main Content

Continuing the series on The Great Football Project…

When we last left off, recall that I had used Excel-linked tables to clean all of the “no name” players out of the DimPlayers table, creating a CleanPlayers table to take its place.

Clean Players Table

I pretty quickly realize that I still have two problems:

  1. Hey, not all of my remaining players have BirthName’s! (First Names)
  2. I don’t have a FullName column, which is gonna be awkward when I build reports

Turns out that I can solve both problems with a single calculated column.  Time for some…

DAX Formula Language

DAX is the formula language of PowerPivot.  DAX really could have been named “Excel Formulas++” but we went with the catchier name.  Because really, DAX is just Excel’s formula language with a number of nifty improvements.

I use Ctrl-RightArrow to jump over to the rightmost edge of the table, since PowerPivot supports commonly-used Excel keyboard shortcuts, to the Add Column:

Adding a Column

And then I start to type the following formula:


and see:

PowerPivot Formula Bar

Let’s stop there briefly, because there are a few things worth noting already:

  1. I’m starting my formula with the typical “=” Excel syntax
  2. It uses the IF function, and you can see from the function signature under the formula bar that it’s the same IF function from Excel
  3. All column names in PowerPivot are wrapped in square brackets
  4. Once I enter the open square bracket, I get AutoComplete against all columns in the current table! 

    OK, let’s just finish the formula.  I notice from the dropdown that there is a [NickName] column, and I remember that in this db, most of the time you want to use [NickName] over [BirthName].  So I’ll fall back to using [BirthName] only when there is no [NickName]:

         =IF([NickName]="",[BirthName]&" "&[LastName],[NickName]&" "&[LastName])

    (I used the & operator instead of =CONCATENATE for readability reasons.)

    I rename the resulting column to be FullName and I’m done.  I’ve added an expression-based column to a state of the art, in-memory, column-oriented store (the PowerPivot db), but I don’t really think about it because there was no SQL syntax, no MDX, no VB expressions.  I just added a calc column using Excel syntax.

    FullName Column 

    Relating this to the FactEvent table

    The FactEvent table, that contains the plays themselves, does not contain player names – it contains player ID’s.  This is good db design, but it’s typically a pain for Excel users, since Excel’s features pretty much only work on single tables.

    Excel users denormalize multiple tables into one by using functions like =VLOOKUP() that get the job done, but are tedious to write, are often slow to execute on large datasets, and result in large spreadsheets.  Plus they have to write one such formula for every column they want to grab from another table.

    PowerPivot really shines here, and I suspect Excel pros will adopt it for some of their work based simply on this one capability:  PowerPivot is a version of Excel that supports relationships between tables.

    Conceptually Similar to VLOOKUP

    If I want to think about relationships in a similar way as VLOOKUP’s, I can.  I go to the “big” table – the one with the most rows, the one I want to augment with data from other tables.  In this case, FactEvent.  On the Table ribbon tab there’s a Create Relationship button:

    PowerPivot Add Relationship

    This brings up the following dialog:

    PowerPivot Relationship Dialog

    I’ve filled in the right values in each dropdown, and done so thinking as if I was writing a VLOOKUP (well, ok, a VLOOKUP where I can match on any column, not just the first column of the lookup table!)

    …and if I happened to get the two tables reversed in my head, no worries, PowerPivot figures that out for me.
        And for my next trick…
        Pretty cool and all, but the real fun starts after I have the relationship is in place.  Fireworks
        in tomorrow’s post. 
          Next Football Post:  The Payoff of Relationships and Formulas >>
        Rob Collie

        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 15 Comments
        1. Ok, being able to define table relationships is really cool. But does PowerPivot import existing foreign key relationships from the db if they exist?

        2. Good question, I had forogtten to mention this. Yes, it does import relationships if they alreadty exist in the db. This is true whether you import tables one at a time or in bulk.

          My original source db contains no relationships, which I am told is not atypical for a data warehouse – performance reasons.

          This must be true, because Donald Farmer told it to me 🙂

        3. How will the SSAS datasource key be defined in the table? Like the uniquekey in reporting services (the MDX representation of the key)? I hope in a somewhat friendly key so i can join it to a non SSAS source.

            1. I mean when we import SSAS as a datasource, when you want to enrich your traditional BI with some adhoc data. Since SSAS is the most clean and structured data concerning a subject i think this will be used a lot. (I know my clients really want top use this.

          1. OK, I didn’t forget you, just had to ask some questions. I got this from Olivier on the PowerPivot team:

            Hey Rob,

            To import from an SSAS database you need to specify an MDX query – don’t worry, we provide the same nice graphical query editor as in Report Builder to make your life easier 

            Then we simply flatten the resulting dataset before we import it into PowerPivot ; so basically the data that you see in the model after importing, is the same that you saw in the query builder while authoring the query: what you see is what you get!

            Of course you can use a data table that originated from SSAS just like you would any other table in a PowerPivot model; if you want to join it to other tables, simply make sure to include the right attributes in your MDX query – or you can also define new calculated measures at the query level to expose more useful data from the cube.

            No member unique names unless you explicitly add them (e.g. via calculated measures) to the query. And we do not detect relationships between datasets originating from the same or a different SSAS cube.

            Hope that helps,

            1. Thx Rob (and Olivier),

              So it works like making a report in reporting services on SSAS, pretty clear and easy! One thing to keep in mind while developing a Cube, we usually make a dimension attribute with “code – description” but we now have to add a Code field as well to make combining with other sources easier. Maybe i’ll create a “prepare your SSAS cube for PowerPivot” guide when CTP3 is released..

              thanks for the answer!


        4. I am very interested to see how the table relationships are set up. Will this work simply with tables on separate sheets within the workbook itself? Will these new relationships export to Access?
          Billy Gee

          1. Welcome Billy!

            We have to be careful now when we use the word “sheets.” There are Excel sheets (in the Excel window), and there are PowerPivot sheets (in the PowerPivot window – two windows operate on the same file, just like VBA). PowerPivot relationships only work between PowerPivot sheets.

            Note that you can always paste (or paste with refreshable link) an Excel table into PowerPivot as a new PowerPivot sheet, and then build relationships from there.

            These relationships don’t export to other apps. They are just used within PowerPivot.

            You say you’d like to see how they are set up – can you be more precise? I assume you don’t mean how the user sets them up, since that was in this post.

            Happy to help,

            1. Hey Rob:

              The idea of being able to import Excel data into PP from an Excel Table(which could be maintained in the Workbook or could be the result of a QueryTable on a Worksheet), with a refreshable option, is huge news to me. It opens up a lot of potential scenarios, by allowing me to integrate Excel data with Relational data using user-defined (or inherited) relationships between fields.

              Very exciting !

              Biggus Dickus

        5. Ok Rob, here i am again with a new question. I was testing something (for a POC with a client) where i had a fact table with Period_Year and Period_Month fields. Now i want to add a new Time dimension table, how can i create a relationship with 2 fields? I couldn’t find it so i tried adding a new column where i Add a formula with Year + Month, you can guess what happens, he adds them instead of appending them. So 2009 + 1 becomes 2010 instead of 20091, I understand why int + int = int. But I couldn’t find a conversion function.

          Can you help me out?


        Leave a Reply

        Your email address will not be published. Required fields are marked *