The first sentence of each blog post is always the hardest. Whew! Glad that is out of the way. Movin’ on!
Part of me wants to skip or delay this post – as I have it on good authority that the Italians are going to write this up for us. And they actually understand this, where I stumbled onto this and was completely confused, flabbergasted, perplexed, baffled and mystified! (I may have pulled out the thesaurus, but you can’t prove it). But, I can’t get it out of my head either… so here we go!
This will take a bit of review first, but as some old dude said: “People need to be reminded more often than they need to be instructed.”
Relationships Have Direction
I have a super simple model. These two tables are tied together by a Name primary key. The lookup table has an Age, and apparently I have a column called “SomeValue”, and measures [Total SomeValue] := SUM(Facts[SomeValue) and [Total Age] := SUM(Names[Age]).
So, can you look at this Pivot Table and guess why the Ages look wrong?
Pretty much any time you see repeated values like this you are thinking “Those values are NOT being filtered”. Here it is because the Row labels were drug in from the Facts table, instead of the Lookup table (Names). As such, the Age column (which is on the lookup table) isn’t being filtered… because the filters only flow from the Names (lookup / one-side) to the Facts (fact / many-side) of the relationship. Relationships have direction…
Aggregate Functions With and without Filter Context
Okay, so… we throw a calculated column on our Facts table. =SUM(Facts[SomeValue]) and the results are vaguely weirdish (at right). The reason is that there is no filter context, so the SUM is operating on the whole table.
Now, if we simply wrap the formula in a calculate, we get something different… because the CALCULATE function has the impact of converting a row context into a filter context.
=CALCULATE(SUM(Facts[SomeValue])). Results below.
Hmmmmm. So… ok. Most of the rows look like what I expected. We stopped running a SUM on the entire table… so, we apparently have a filter context, but that blank on Joci is kinda weird. That is our first inclination that… something is going on here that isn’t quite expected.
Digging Further Into Context Transition
I have added just 2 more rows. Another Leanne row, and another Rob row. However, Leanne got a unique value for her SomeValue (77), where Rob got a duplicate value (66 again).
And the results are interesting. Leanne maintains a sort of “per row” looking filter context, and just get a new 77 calculated column. But that tricky Rob… because every column for him was identical… his filter context includes BOTH rows on his calculated column… adding his two 66’s to get a 132. In both rows.
So, what we learned is that the filter context created by CALCULATE is not some magic “I only filter down to THIS row”, rather it just has… Name=Rob, SomeValue=66, etc… for each column. And it can very much match multiple rows.
Now about that Joci… the thing is, she is not in the lookup table. I’m so trixsy. But like… why would that matter? At all? I mean… it’s not like we are doing anything with that lookup table. We are just walkin’ the fact table doing some calcs. Why is it even doing a lookup!? We are still weirded out at this point.
Row Context, Lookup Tables and Blown Minds
Before I continue, one minor point that I’m not going to paste a pretty picture for… just take my work for it. Measures (Calculated Fields) have an implicit call to the CALCULATE function. They do the magic context transition from Row to Filter, regardless of if the CALCULATE function is actually called.
Okay, so I am going to define 2 measures here:
And drop them onto our fact table… and check what happens.
Okay, the FactRows… looks like what I would expect based on our previous learnin’. But… those NameRows? Those are from the lookup table (the “one” side of our one to many), and we had tried to say that “Filters Have Direction” – heck there was a whole section of this blog entry on it. And it shows the same value for every row when using rows out of the fact table. Which is totally what we are doing here. Wha?
So… that is the weird and Mind Blowing new learning. During context transition (converting row context to filter context)… the new filter context will follow to the one side of all relationships (what we like to call the Lookup tables). Which… totally feels like a filter flowing in the opposite direction of we typically expect. Or at least… what *I* expect.
I am going to add one more fact table just to demonstrate these filters also flow “back down” in the typical direction of lookup table down to fact table. Which is neat. I think
So, the structure looks really typical. One fact table is filtering two different fact tables. What what we are going to see… is that when adding a calculated column on the Facts table, the freaky context transition will allow us to pull values from Fact 2… that correlate to our primary key. Without writing ANY custom filtering code. It just magically works, which… is awesome or scary or something.
The new measure is just: [Total Other] := SUM(Fact2[OtherValue]). And I drop it on our (original) Facts table, and check these results. Since only Leanne and Scott were in the Fact2 table I pasted above (aaaaah, cute… we are probably kissing and stuff!) only the Leanne and Scott rows get values in our original Fact table where we dropped this new calc column.
So, we start from the (left) “Facts” tables and on the context transition we filtered the Lookup table (the “Names” table on the one side of the one to many), and then THAT filtered down into the (right) “Facts2” table… giving us… well, I would say… the values we wanted, but maybe not what we (or at least I) expected when I first saw this.
Wrap It up!
That’s pretty much a wrap. The only other thing I will say is that… this doesn’t only happen when doing calculated columns. That may be the most common place to think about this row to filter context transitions… but they can also happen on any of the iterator functions that create a row context… say, SUMX() or FILTER(). I kind of want to pretend I don’t know that… cuz I am barely holding these concepts in my head as it is.
And… I should probably mention that… this is advanced stuff, in my option. If you don’t understand it… I wouldn’t stress it. I have built MANY models and gotten tremendous insights… without understand this… at all.
For now, I am going to back-burner this. Keep enough awareness so that when weird stuff happens I can say … “oh yaaaaa…. there was that weird filtering behavior I didn’t really expect…” and go about my happy life.
If you love this though, be on the lookout for a post from the Italians. I suspect they are busy traveling to MVP Summit and PASS 2014 right now… but Marco promises he is going to write this all up, since it has come across his desk a few times. Likely it will be a more thorough treatment… I know I am looking forward to it