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:
With the table sorted, though, I notice that there are multiple SeasonNumber values corresponding to PlayUniqueID = 1:
…so I add a calc column concatenating the two, with renewed hopes for uniqueness:
And… curses, no such luck:
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()
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:
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 >>
