We've officially reached measures now the really deep part of power, pivot and power by that will astound,
amaze, confound and positively anger you on occasion.
But why?
So far, our measures have been incredibly simple.
The formula we've used is simply equal to some of the field and it just seems to work.
But think about that for a moment.
We have a bunch of conditions in our pivot table.
How can our power pivot do these sums if I haven't given it conditions?
What if I want more complicated conditions such as a cumulative running total of our profit?
Ultimately, all of this is going to boil down to context.
Every tax formula calculates based upon the data context in which it is encapsulated.
This pivot table starts out with thirty six context sets.
Once we expand it like this, it's up to over 90.
I keep saying this word context, but what what is it I'm talking about?
Well, the context refers to the data universe as it relates to a particular value cell in our pivot
table or in power by a particular point in a chart.
That means that if we look at this particular cell, the context is all of the data that's available
that has a relationship to Kroger in the stores table and relationship to the quarter three thirty one
twenty nineteen in our calendar table.
So think about it is starting with all of the data and then it gets filtered down by relationships,
note that any unrelated tables would not be filtered.
This can come in handy.
There are ways to modify context within a DACS formula to create alternative context implications for
a given calculation.
But before we figure out how to do that, let's identify an example where we might need it.
If we think about how our relationship definitions work, they limit tables to those entries where the
target values match up.
In this case, we want our measure to capture all of the values that are less than are equal to a given
date, not just equal to it.
Since our relationships aren't up to the task, we need to create a more complicated measure that takes
into account some of our relationships, but not all of our relationships.
And then we need to define a customer relationship on top of it.
This sounds pretty difficult, but if you think about how we might do this with a service function,
it's only a mile change with power pivot.
It's a little bit more complex in that there are two steps to it and unfiltered step and then a filter
step which will be tied to our measure.
All of this is going to boil down to understanding and managing the measures context, so let's really
dive into what context is.
Context isn't.
The hardest part of context is that it's very contextual, obviously the context for a measure is computation
will be based upon where the calculation occurs within an output.
So let's look at our pivot tables for a few examples.
Each of the cells in our pivot table can be imagined to have a full series of tables behind them, each
with a different context related to them.
The cell where three thirty one twenty, nineteen and Kröger intersect has access to all of these table
data sets, all of them filtered to three thirty one twenty, nineteen and Kröger.
Moving to Tom Thumb and we would have Tom Thumb is the filter, so when we request the sum of the purchase
orders or the sales field within the context of that cell, it's calculating the entire sum from that
table.
There's no if computation involved at this stage anymore, which seems odd since we think about it as
being a sum if computation some if the is Kroger and the quarters.
Three thirty one twenty nineteen.
So in this calculation occurs, it's simple, it's fast, it's efficient.
So this raises a question, are there ways to manipulate the context within the computation directly?
Yes, we can modify our measures so that we can manipulate the context in quite a few different ways.
So let's discuss some of the general concepts and functions involved.
The most important function to know is the calculate function, which is where we'll start from.
The calculator function takes some type of computational function or command, such as are some function,
or it can take another measure and it applies one or more filter modifications to it.
In this case, I have simply written a filter that filters down to the store in the sales table equals
the store value.
Obviously this will calculate the exact same results as before, which I can see when I add it to the
pivot table.
But what if we wanted to create a weekend sales total?
Obviously we could add the weekend identifier to our table and modify the context that way.
But in this case, I'm going to go into the measure and modify it so that it has a filter specifically
to weekend.
This filter function adds filters, but we can remove them as well.
There are quite a few different filter removal commands, many of them start with all I'm going to use
the general all function here.
When you use the URL function with a specified field or table, it will remove any filters that are
applied specifically to that table.
That's very odd language to say filters applied to that specific table.
Well, this is where our dimensional tables become very important.
Envision our little relationship here between calendar stores and sales.
When we have store selected as Kroger and our quarter selected is three thirty one twenty nineteen.
We're not directly filtering our sales table.
We're applying our filters to the stores table into our calendar table.
And then we're using these filtered tables and the relationships between these tables with the sales
table in order to get the proper data.
So let's say I wanted to remove my date filter limitation and get just the total for stores across all
dates.
So in other words, I want to ignore the date filter while using the calculate function and the sum
of sales.
I can add a filter that says use all from the calendar table.
Now, with this measure, the context of the cells within the pivot table will always filter only by
store.
I can remove quarter from the pivot table and we can confirm the totals real quick.
As you can see, this all dates version calculates the total ignoring the date field just as we've liked.
Pretty nifty, but what if we wanted to refill targets for a cumulative approach?
This is where we'll really take advantage of some context.
Magic.
If we go look at this next measure, we're going to calculate our sum and apply a compound filter.
The first step in the filter says to give us all of our calendar table, stripping away the calendar
restrictions.
And then it adds a new filter to our calendar table that says to pull the calendar date when it is less
than the maximum calendar date.
That what that there's no way that doesn't make sense.
That's just going to pull all of the calendar dates.
Right.
Let's go at it to the table and see.
When we add it, it looks like it's a running cumulative total, but why?
Context, as always, if we look at our measure and we dig into this filter function a bit.
We could figure this out, what would we get if we calculated our Max calendar date while in this three
thirty one twenty 19 cell independent of all other factors?
Well, because we have relationships, the largest state within this context would be three thirty one
twenty nineteen.
That is the max calendar date isn't the maximum of the table in total.
It's the maximum of the calendar date.
With respect to the context, outside of the calculated function, if we moved out to sell our new max
date would be six thirty, twenty, nineteen.
Since we're now in a different context, giving us a new cumulative total, we'll dig into this in a
little bit more detail as we continue.
But with that little bit of context magic, we've created a new, more flexible measure that accumulates
our sales together.
Join me in our next lesson as we start changing our measures together and we'll continue discussing
and expanding on the beauty of tax context.