Welcome to MSPTDA
video number 19. Hey. In this video we got to talk
about the most epic function in DAX, the CALCULATE function. And we have to talk
about filter context. These two work together. Yes, we have filter context
from the external report. Those are the
conditions or criteria that help filter our
underlying data model to get our conditional calculation. But the CALCULATE function
is the DAX function that allows us to change
the filter context. Also in this video, we'll
talk about ALLSELECTED and KEEPFILTERS, two mysterious
functions that interact with the filter context. And of course,
since we're talking about DAX and data
modeling, all the topics we cover work in Power BI
Desktop or Excel PowerPivot. Now, here are some
of the topics we're going to cover in this video. And if you want to
follow along, there's a number of different files
that we're going to use. This is the file we're
going to start off with. Now, over on the DM for
data model one table, we're going to start off with
a very simple set of tables. There's our fact table,
fTransactions with an s, and the dProduct table. I've already imported these
into the data model, so PowerPivot data model. In our PowerPivot window,
there's diagram view. We can see we have a simple
fact table and a many to one relationship with
our product table. We've already
calculated total sales. If I go over to data
view, fTransaction table, total sales is a
simple sum function. But let's start
off with the basics of what is filter context. Alt Tab, jump back
over to Excel. I'm going to click
in a cell, go up to Insert Pivot Table,
or the keyboard, Alt, N, V. It knows
we have a data model, so it's automatically selected. I click OK. Open up fTransactions
and dProduct. We're going to drag product
from the dProduct dimension table down to rows. Total sales, f of x means
that's a measure down to values. Now, what is happening here? That is a condition or criteria
from the external pivot table over in Power BI Desktop. It could be any one
of the visualizations. But we almost always have
conditions or criteria externally that are going
to flow in and influence the measure. Over here we can manually
see that these three rows were used in the
adding process to get 35. But internally, underneath,
the Quad criteria condition from the external filter
context flows into the measure and helps filter the table. Now, visualizing this, we go
from the full product table, it gets filtered down to just
a single row showing Quad. Because there's a one to many
relationship with an arrow, that arrow indicates the
direction of the filter and how it flows. That Quad filter flows across
to the fTransaction table, filters the table down
to just the rows we want. So that means the
Sales column has only the numbers for
that particular condition or criteria. Then once the table is
filtered, then the measure makes the calculation. Now, if you think
about Excel and you know array formulas, when you
make conditional calculations, it has to iterate
over every single row, even if the rows don't
meet the condition. So one of the beautiful things
about the data model and DAX formulas is this
filtering ability to make whatever
column we're making a calculation on smaller. Then the formula
makes the calculation. Now, so far what we've
talked about in this video we've already talked
about in earlier videos. But in a video
about CALCULATE, we want to make sure we
have all of the basics down before we get to the much
more complicated CALCULATE DAX function topics. Now, we have external
filter context. Now we need to talk about
the CALCULATE function, how it changes the filter
contexts, and the filter contexts inside the
CALCULATE function. We can go over to
our data model. Below total sales, we're
going to create a measure. I'm going to click up in the
formula bar, colon, equal sign. And the letter C will always get
me to the CALCULATE function. So I can simply type
C and Tab, expression. Now guess what? We're using CALCULATE, and it
expects a scalar expression, that means some measure that
calculates a single answer. We could equally
use CALCULATETABLE. And in fact, most of
what we do in this video will be all about the
CALCULATE function. But everything we
learn about CALCULATE is equally applicable
to CALCULATETABLE. CALCULATETABLE expects
a table, and then we change the filter
context for the table. CALCULATE takes an
expression, some measure, and changes the filter context. Square bracket,
and our convention is always to put our measure
name in square brackets only, comma, and the filter. Now we can do multiple filters
and then CALCULATETABLE and CALCULATE. All of these filters
will work internally as an AND logical test. Now, I'm going to backspace. We simply want to go to the
dProduct table, down arrow to the Product column. Then I'm going to type
a Boolean logical test. Hey, everything in that
column, are you equal to, in double quotes-- this is text-- the Quad product? Close parentheses. Now, a Boolean filter
means we have some column. There's a comparative
operator and then some value that we're going to
check for in that column. And we'll talk a lot
about Boolean filters through the first
couple of examples. But right now all
we want to see is we have a new filter
internally inside our formula. Another important thing about
calculating CALCULATETABLE as opposed to the
FILTER function, which we saw last video,
in the CALCULATE function we can use any
column from any table in the entire data model. That's different than
FILTER, last video, where we had to use a column from
the table in the first argument of the FILTER function. All right. We have our internal filter. I'm going to hit Enter. Now, over here in
the measure grid, there is no filter context. Or wait a second. There's no external
filter context. But inside the formula,
there's internal filter context giving us the total of
$35 for the Quad product. Let's go drop this in
the pivot table, Alt Tab. Over here I see my new
measure, Quad total sale. So I drag it down. And look at that. I get $35 everywhere. Now, where would you
use something like this? Well, maybe you're comparing
each one of the other revenue amounts for the product
against the Quad product. But for us, it's
going to help us illustrate internal and
external filter context. Now, as a basic lesson of how
this measure is calculated, before it ever filters the
underlying tables, it has to decide, since
there's the Product column inside the measure
and the Product column is in the row area
of the pivot table, it has to decide
which column to use. Do I use the internal column
and the condition Quad, or do I use the external column
and the condition Carlota for this row or
Quad for this row? The way it works is this. When it's given internal
and external filter context and the column exists
in both places, the internal column
and its filter will replace the external. Now, technically
what happens is, since there's
external and internal, for a moment the column
exists in both places. But once the engine realized
that it's in both places, it removes the external
and keeps the internal. Then it will run an
AND logical test. Now, here it doesn't
really make sense because once it
removes it out here, there is nothing out here. But later we'll see
that when it removes a column from the
external, there might be external other filters. So the column exists
in both places. External one is removed. It's run as an AND logical test,
which means no filter out here, Quad here, and that happens
for every single cell. And that's why we get
$35 for every single row. For our next example, we're
going to use this Power BI Desktop file. And what we'd like to do
is use the same Boolean filter in CALCULATETABLE. Here's the same fact
table, relationship, and dimension table. Over in table view, I
can come up to Modeling. Click on New Table. We're going to name this
table Quad Transaction, equal sign, C. And
there's CALCULATETABLE. Notice, because I'm in
the context of creating a new table, when I
type the letter C, CALCULATETABLE comes up. Tab. The table we're going to
filter is fTransaction, Tab. And there it is. We can use our filter. Comma, dp, down
arrow to product. Hey, are any of you equal
to, in double quotes-- this is a text, literal
in double quotes-- close parentheses. And now when I hit
Enter, calculate table has taken the full
fTransactions and filtered it. Now, when we create
a table like this, there was no external filter
context on the fTransactions, so we used our internal filter. It created the filter
context, filtered the table, and delivered it
to the data model. Now, over in Excel, we can't
create a table like this and put it into the data model. But over here in
Power BI Desktop, now we have a new table
in our data model. So when we use
CALCULATETABLE or CALCULATE, we can use just
an internal filter to change the filter context. We can use an internal
and external filter and merge them to change
the filter context. But guess what? The calculating
CALCULATETABLE function can do one other amazing thing. It can change the filter context
by taking all available row contexts and converting them to
an equivalent filter context. And that, of course, is
called context transition. As an example, we'll go
over to the data model. We're on the dProduct sheet. I want a new column to
calculate from the sales table the total sales
for each product. I'll call this Calculated
Column Product Sales. And up in the formula bar
we'll type sum, ft, down arrow to sales, close parentheses. But guess what? This is an aggregate function. There is no external
filter context here. So when I hit Enter, I get
the grand overall total. Now, maybe that will be useful
in a particular formula, but that's not
what we want here. I want that Quad
condition or criteria to filter the fTransaction
table in that cell. When I get down to
this cell right here, I want Carlota to filter
that fTransaction table. Well, the CALCULATE function
comes to the rescue, because the CALCULATE, if
I wrap it around the sum, will take any
available row context and convert it to
filter context. So before sum, type
the letter C, Tab. Now, we don't have
any filters here, although you could
put them there. So all we're going to do is
put the aggregate function and expression,
close parentheses. And when I hit Enter,
now $35 right there. That's because the Quad
row context converted to filter context filtered
the fTransaction table. Now, the CALCULATE does
oh so many amazing things. If I create a second column,
product sales two, and Enter, this time I'm simply going
to square bracket, t. There's my measure, total sales
when I hit Tab and then Enter. Oh, as we studied earlier in
the class, every single measure you ever use has a hidden
calculate function wrapped around it. So anytime you use
a measure anywhere, you have to be aware
that the formula will pull any available row
context and convert it to an equivalent
filter context, which, of course, is exactly what we
want in this calculated column. So the CALCULATE function
can use just internal filter contexts. It can merge external
and internal together. It can do context transition. And all measures have
a hidden calculate. Now, our next topic,
we're going to go over to the fTransactions,
and I'm going to click on my Boolean
filter inside CALCULATE. And we need to talk more about
what's exactly going on when we build a Boolean filter. Now, what's really going on with
this Boolean filter is actually there's a little mini
table right there that has the product field name
and the one condition Quad. Now, that's not
what we put there. We put column, comparative
operator, and criteria. Every single time you do
that, here's what's really happening behind the scenes. So I started our third
measure, and I named it Quad Total Sales FILTER ALL. There's the measure. And to create that mini table,
we use the FILTER function. Now, the FILTER function
will deliver a table. What we need is a
table to filter. And here's what the Boolean
does behind the scenes. It will always use the ALL
function on whatever column we used in the
Boolean expression, down arrow to product,
close parentheses. The reason that the DAX engine
uses the ALL function to create the table is because the
ALL function does not see the external filter context. Because the goal of our formula
when we're doing Boolean is to show Quad in
every single cell, we need to have a
table that's not going to be influenced
from the row conditions or criteria for product
from the pivot table. So ALL is exactly the
correct table function that we want to use
in the first argument of the FILTER function. The ALL function will deliver a
unique list of all the products from the Product column. Then it internally will repeat
exactly what we typed out. Hey, that particular column, are
any of you equal to the Quad? Close parentheses. So this construction right
here, a lot more typing to do, but that's exactly what happens
internally in the DAX engine anytime you create
a Boolean filter. Now, I'm going to close
parentheses and Enter. We could see it
gives us 35 here. I'm going to add some
number formatting. We're going to go drop
it into a pivot table, but then we're going to
go over to DAX studio, because I want to show you
exactly what's happening with that formula. Alt Tab. And now over in our
pivot table field list, there's the filter ALL. Drag and drop. So it will do exactly the same
thing as our Boolean filter that we typed out,
but internally it's using FILTER and ALL. Now, luckily we don't have
to type out FILTER and ALL every time. We can do just a
Boolean expression. Now, let's go over
to DAX Studio. I've already downloaded this. We've used this a number of
times earlier in the class. I'm going to click DAX Studio. Because we opened it from within
Excel, it sees our data model. I'm going to connect. Over here on the left
I'm going to open up dProduct and fTransactions. Over here on the white, I'm
going to Control and roll. This is where we
can create our DAX query to evaluate
table functions and have a preview
of them below. We use the EVALUATE command. And we're going to start
with ALL on the Product column, close parentheses. Up here, Clear
Cache and then Run. I'm going to run this. So we can see the ALL will
always give us a unique list. And notice there's a field
name at the top and then the unique list. Now we're going to use
this inside filter. So there's the table
in the first argument of filter, comma, Enter. Double click Product over here. Are you equal to,
in double quote, Enter, backspace, and
close parentheses. Now, as the formula
element that is internally created when we
type out a Boolean, it does what it's
supposed to do. It takes this
condition, and there's the table in the first
argument of FILTER. It iterates over each
column, gets a true or false. There's only a true for
Quad, so this whole thing, when I click Run,
evaluates to just what we need inside the
calculate function filter argument, a little mini
table, a valid list of values that we use to figure out
which records to include. This is the internal
filter context. Now, there's another
important insight we can gain from understanding,
when we type a Boolean, then internally the DAX
engine uses FILTER and ALL. If we go back over to
Excel, remember, the goal of this formula, Boolean, or
internally, the FILTER and ALL, is to calculate the same
number in each cell. That will always happen
when we do a Boolean. How does that happen? Well, guess what? From our study last video
about table functions, we know that the ALL function
doesn't see the external filter context. So for every single cell on
the pivot table or the Power BI visual, this ALL
function will always deliver a complete
unique list of products. That's different than
the VALUES function. If internally it used
the VALUES function, we would not get the same
value in every single cell. Now, I actually want to go try
that and compare and contrast the code for a Boolean and if
we use the same construction but with values. Let's go back over
to our data model. I'm going to copy this
FILTER and ALL formula, Control C, Escape, come
down one cell blow, up to the formula bar, Control
V. And instead of FILTER ALL, I want to type values. And then instead of in the
first argument of FILTER, I want to type VALUES. Now, this is not what
is generated internally when we do a Boolean value. If we want the result
of this formula, we're going to have
to type this one out. So what's happening here? We have our FILTER
function, which is going to deliver a
valid list of values to the CALCULATE function
to calculate total sales. But the difference is VALUES. We'll see the external
filter context. VALUES will always
be a single row table for each row
in the pivot table. The filter context will flow in. Let's see in the pivot
table how this works. Of course it's going to run
an AND logical test now, because the FILTER
function right here was iterating over a single
table that had Carlota. It asked, is Carlota
equal to Quad? False. It got an empty
filter, so nothing. Here, the values function was
looking at a single table. That means this condition or
criteria flowed into values, values had a single
Quad table, and then the condition was asked,
hey, are you Quad? True. So if we want Quad total
sales in every cell, we just type out a
straight Boolean. If we want something
different, then we're going to have to
build our own table function to deliver the
correct valid list of values to calculate. The next topic we
want to cover is, what if we have a Boolean filter
but we do not want to hard code the value into the formula? We want to pull that
condition or criteria from a table in the data model. No problem. We can do that with
the VALUES function. Over here in Excel, we have
a fact and dimension table. But if you scroll down,
we have two other tables that will help us illustrate
all the different awesome things with CALCULATE. Here's a variable table. That's the condition or
criteria I want to pull from. Now, I've already imported
this into the data model. Over here in diagram
view, there's a single column and single
row table with our variable. Data view. And I want to use the
same formula, Control C, Escape, down below. Up to the formula bar, Quad
Sales, criteria from VALUES. And we want to, instead
of having hardcoded value, we're going to use the VALUES. Now, dis means
disconnect the table because there's no relationship
between this table. There it is, the
Variable column. Close parentheses. Now, the amazing
thing about VALUES, and we've talked
about this last video, it can deliver a unique list
of values in the current filter context. But if it's a single
value, it will convert it to a scalar value which can
be used in the formula, which is exactly what we need for
our condition or criteria in a Boolean calculation. Also, since this
is disconnected, there is no external
filter context, so VALUES on this
column will always give us just that one
single input value. When we hit Enter,
we get our 35. We can add some
number formatting. Back over in our pivot
table, we can click and drag. Right now we're getting 35. But if we change this to Carlota
and refresh our pivot table, now we get 45. Hey, that's not a good name. Back over here on
the data model, I'm just going to change
this to Total Sales Criteria from VALUES, and ENTER. And there we go. It has updated. Now, we need to talk about
AND and OR logical tests when we're doing a Boolean
filter inside the Calculate function. Back over here in
the measure grid, we want to create a
measure that calculates the sales between $10 and $20. And we're going to do it using
a single filter argument. Now, we're going to ask the
question of the fTransaction Sales column, how many
sales in this column are greater than or equal to 10? And because we're
going to construct it in a single
argument, we're going to learn a very different AND
logical test operator over here in DAX. Ampersand, ampersand. One after the other means we're
doing an AND logical test. FTransactions of the
sales, the upper limit will be less than 25. Close parentheses. Now we can hit Enter. There's 75. That's the answer. Add some number formatting. How did this filter work? Well, here's our fTransaction
table before filtering. Here's our two conditions. It ran that condition
across the table, getting trues for all the rows. Then it took the total
and added to get 75. Now, doing a double ampersand
to run an AND logical test in a single filter
argument, that's not the only way we can
run an AND logical test. If we come down to this
next measure right here, well, of course, since CALCULATE
gives us multiple arguments, and they together will
run an AND logical test, this is another way we can
construct a Boolean AND logical test. Still another way,
the AND function. The AND function will allow us
to do it in a single argument without using that
double ampersand. Now, we've got to talk
about a very important rule or restriction for Boolean
filters inside the CALCULATE function. We used double ampersand. In just a moment, we'll also see
the double vertical bar or pipe for the OR logical test. But when you do this, you
cannot use two different columns in the same single argument
Boolean logical test. Now, there's easy ways
to get around this. But we have to remember that. And, in fact, if we try
it, which we will later, we'll get an error
that reminds us, you can't use two different
columns in a Boolean test in a single argument. Now, the reason that you're
not allowed to do this is because of the DAX engine. The DAX engine has a hard
time taking two columns and converting
them into that ALL function in the first
argument of FILTER. And if they have to replace
columns in the external filter context, they don't know what to
do when they have two columns. Nevertheless, I'm going
to hit Enter here. And now we'll see
the simple solution when we have an
AND logical test. With this measure, I want to
calculate the Carlota sales when the transaction
amount was greater than 15. So dProduct, we'll
do the product. Hey, are you equal to Carlota? And just for a moment
I'll make our error. Double ampersand, fTransactions
sales, are you greater than 15? When I hit Enter, it's polite. This expression contains
multiple columns, but only a single
column can be used in a true/false expression that
uses a table filter expression. Well, in this
case, we can simply backspace, comma, and very
carefully come to the end, and Enter. And there's our total. Now, if we look over here in
our fact table, we can see, here's all the Carlota records. Did the filtering work to
get the correct total of 40? Yes, it did. These two transactions
are both greater than 15. When we add them, we get 40. Now, we saw four examples
of AND logical tests. Now let's do an OR logical test. Now, this measure's called
Freestyle Boomerang Sales. And that's because
this group is made up of the Carlota boomerang
or the Quad boomerang. Now, in our
transactional data set, we only have two different
products that we sold. But here's how we do it
as an OR logical test. Hey, product, are
you equal to Quad? And then, just like
there's a double ampersand, we have to find the
vertical bar or the pipe and put two pipes,
one after the other. That in DAX means
an OR logical test. Then we can check and see if
the product is Quad or Carlota. Close parentheses and Enter. Now, we got away
with this, of course. Single filter
argument, two columns, but they're both
the same column. Now, there's an alternative. I'm going to copy
that and down below. And I'm going to
call this one OR. And instead of using two pipes,
we can use the OR function. Just like there is an AND
function over here in DAX, there's an OR function also. So when I hit Enter, I
get exactly the same. Now, if we had two
different columns, we could not use
the OR function. Because, guess what? Those two Boolean
logical tests are still in a single filter argument. So unlike an AND
logical test where we're allowed to use
filter two, filter one, we can't do that with
an OR logical test. Later in the class we'll see
how to do an OR logical test on two different columns
using the filter function. All right. So we saw four
examples of Boolean AND and two examples of
Boolean OR logical tests. Now, for our next
Boolean example, I would like to, inside the
filter argument of CALCULATE, compare the Price
column and the Cost column from the
dProduct table directly. Now, in retailing, there's
something called keystone. Keystone means when the
price is double the cost. So I want to ask the question
inside the formula, hey, how many of the products
have a price that's double or greater than the cost? For Quad, we can see that
43 is not double the cost, so we do not want to include
that in our calculation. But Carlota, that price
is double or greater than the cost, so I want
to include this product in our calculation. So in our measure, we're
going to directly compare these two columns and
calculate total sales for keystone products. Now, this measure is going to
be called Total Keystone Sales. There's our measure
in the expression argument and our filter. DProduct, and I'm going
to down arrow to price. And I'm going to say, how
many of you in that column are greater than or equal
to items in the Cost column? Times two. Close parentheses and enter. Well, of course I'm going to
get the same error as when we tried to do this before. Two different columns in
the same filter argument, it's not going to work. No problem. We can put this
logical statement into the FILTER function. The only question becomes,
since we're using FILTER, these two columns both have
to be in whatever table we put into the first
argument of FILTER. I'm going to use the ALL
function, close parentheses, comma. And as we learned
last video, ALL works on a single
column, multiple columns, or a full table. I'm going to do dProduct,
price, comma, dProduct, cost. ALL will give us
all the combinations between prices and cost. The FILTER expression can
now iterate over this table, get true or false, and FILTER
will deliver a filtered list of valid rows from
the product table that CALCULATE will use
to filter the sales. If I come to the end and
Enter, we get an answer of 45. If we look over at our
fact table, we can see, since the Carlota is
the only product that's keystone or above, when it
filtered the fact table, the measure got exactly $45. Now, I'd like to go
copy this formula element, Control C, Escape. We'll go back to DAX
Studio, Control V. Now we can add a little
formatting over here in DAX Studio,
Format Query button. And when we click Run,
there's the price and cost for the only one record in the
product table that matches. These values will
allow the product table to be filtered down to
just the Carlota row. That filter flows across the
relationship, filters the fact table, and total sales can
calculate the total of $45. Now, for our last
Boolean example, I'd like to go back
to the measure that calculated sales between
transactional amounts of $10 and $20. We did an AND logical
test in two columns. But what I'd really
like is to have, in the row area of the
pivot table, the lower and upper limit. Well, if we go back over to our
Excel sheet and scroll down, here's exactly the
table that I've already imported into the data model. It has the lower and upper
limit, and it has a category. I want to use this
category in the row area of the pivot table,
and then the formula needs to access for each row
the lower and upper limit. Now, we're going to start off. I'm going to come over here
below this pivot table, Alt N, V, data model, click OK. Dis for disconnected table. There is no relationship between
the fact table and this one. Category, I drag it down here. By the way, as we saw earlier in
the class, when I created this, this would not sort correctly. So in the data model, we
sorted category by lower limit. Now, the goal of the
formula is to use the upper and lower limit and count
how many sales amount fit into each category. Sounds like exactly what we did
in our prerequisite statistics class a lot. But now we're doing
it with DAX formulas. So in order to create a
measure, to create our frequency distribution to count
between these categories, we're actually going
to use a great DAX function called COUNTROWS. COUNTROWS will point to
the entire fact table. Then as we iterate through
in the pivot table, each one of the lower and upper
limits, the table will be filtered down to just
the number of rows that meet those upper and lower limits. Then the COUNTROWS will count
the number of transactions. And, boom, we'll get our
frequency for each row. Now, over here in
the measure grid, we're going to create our
first measure, COUNTROWS in Sales Table. COUNTROWS fTransactions, Tab. Close parentheses and Enter. That's six, the overall
count of transactions. Over here in our pivot table,
if we drag our new measure down to values, oh, no, we're
going to get a count of six all the way down. There is not a relationship
between this Category column, and the disconnected sales
limit, and our fTransactions. But no problem. We will access-- from having
this in the pivot table external filter
context, we'll access the lower and upper limit
to filter the fact table. Now, looking back at one
of our earlier formulas, notice we use the
VALUES function to get at a particular value. That's not going
to work for getting the lower and upper limit. Because in the grand
total cell, I'm going to have to pick
out the min and the max in order to get the correct
lower and upper limit. So instead of
using VALUES, we're going to try to use the MIN
and MAX to get our conditioned to use on the other side
of a comparative operator. So down on the
measure grid, we're going to name our
formula Sales Frequency, the CALCULATE
function, the measure we're going to have
in our expression, COUNTROWS in Sales Table, comma. And now we need to change
the filter context so we don't get just a count of six. FTransactions. And I'm going to say, hey, all
you values, how many of you are greater than or equal to
the min of the disconnected lower limit? Now, as we copy
the formula down, the MIN will always pick
out the correct lower limit for each one of those categories
and in the grand total cell. Now I'm going to use double
ampersand because we're doing an AND logical test. And now I have to
say, how many of you are less than the
upper limit, which is the max of the
disconnected upper limit? Close parentheses. Close parentheses. Well, the problem
when I hit Enter, using aggregate functions
on the other side of a comparative operator,
that is not allowed. That's what the error says. Luckily, the FILTER function
will come to the rescue. I'm going to filter what table? Well, of course the
fTransaction, comma. There's our filter expression. This filter expression
in the FILTER function can do a lot more than
a straight Boolean. And in this case,
it'll have no problem using the MIN and the
MAX on the other side of a comparative operator. Close parentheses and Enter. Now we get a six here, because
there's no external filter context. But when we drag, this
shows up because there's not a relationship. It's not smart enough
to know that we have a disconnected
table that's not supposed to have a relationship. I'm going to drag this
down to the values. And just like that, we
get our correct count. And we can check this. How many transactions are
there between 10 and 15? Two. And there they are,
between 10 and 15. Notice the lower limit is
included, upper limit not. Of course, we do
that intentionally, because if we're using the
upper and lower limit in two different places, one of them
better not have the equal sign. The other one better
have the equal sign. Now, taking a closer
look at this measure and how it acts in each
cell on the pivot table, notice the measure there,
fTransactions, all of those do not have any
external filter context. It's only the MIN
and the MAX functions that are influenced by this
column from the external filter context. Remember, the way this table
looks is there's the category. There's the lower
and upper limit. So as this measure
copies down for this row, for example, MIN only sees
the lower limit for that one row, which would be 10. MAX sees only the
one row, max of 15. It's not until this
formula gets down to the last grand total cell
that the MIN and MAX are picking from the whole column of
lower limits and upper limits. Now, to help illustrate how
this formula is calculating, I'm going to do
something kind of crazy here and use an Excel
spreadsheet example to show you what's going on under the
hood in the columnar database in DAX engine. Because really, the hard
part of learning DAX is because we can't visualize
it the same way as we can in Excel. But here it is. We have CALCULATE
working on COUNTROWS. COUNTROWS is counting how many
rows are in the fact table. This FILTER function's job is to
provide a valid list of values that can filter this table. FILTER looks at
the fTransaction. That's the whole
table right there. It's looking at the
Sales column right there. But look at this. The comparative operators are
looking at the MIN and the MAX. But these columns are
coming from this table. Well, let's think about this row
in the pivot table right there. The measure gets filtered
because that condition or criteria from the row
area flows into the measure. That means this table
is filtered down to just a single row. That single row is
what is seen by the MIN and the MAX in this row. Then the two
columns are compared to the lower and upper limit. Once we get a series of trues
and falses for the fact table, then the FILTER function
actually filters the table. This is what it would look like
if we did it in Excel, greater than or equal to
10, less than 15. And now we have
our filtered table. That's what FILTER delivers
to the FILTER argument inside the CALCULATE function. Then once CALCULATE sees this
filtered table, COUNTROWS, the measure, it can do
its job and count and get a count of two rows. Another important
aspect, if I completely unfilter both of
these, that's what's happening down here in
the grand total cell. That's why when FILTER is
looking at the MIN and the MAX down in the grand
total cell, MIN is looking at this entire
column picking out zero. MAX, it's looking
through this whole column and picking out 25. Of course, for the
FILTER function, all the columns are
between zero and 25, so FILTER delivers
the entire table. CALCULATE counts all
the rows in the table. Now, on our next example
of how to use CALCULATE, I want to, in this pivot
table I just created here, compare each one of
the individual items to the grand total. Well, of course, if we're
using a data model pivot table, we then need, in each cell,
to have the grand total. We actually have already
seen this formula earlier in the class. We want to calculate grand
overall total, CALCULATE. There's our total
sales, and we need to change the filter context. And how do we want to
change the filter context? Well, we want to remove
every single filter that might be applied in the
external filter context. Well, the function we
use is the ALL function. Now, we've used the ALL
function last video a lot and in this video a lot. But in this video,
we were using it in the first argument
of the FILTER function. Anytime you put the ALL function
inside the Filter One argument, in CALCULATE, it becomes
the remove operator. So if our goal is to remove
every single filter possible, then in a star schema data
model, we use the fact table. Because all the dimension
tables feed into this if I use ALL to remove
every single filter on every single column,
It'll remove all the filters in the data model. Close parentheses. Now, remember, ALL, we could
put a single column here, we could put multiple columns,
or we can put a table, in our case, the fact table. Now, when we use
this as a filter, every single cell will just see
the entire fTransaction Sales column. Close parentheses and enter. Over here in the
pivot table, now we can drag our grand overall
total down to the values area. And there it is, the same grand
overall total in every cell. Now, as the numerator,
we need to take for each row in the pivot
table our total sales, and then the denominator we'll
use our grand overall total. The measure name will be
percent of grand overall total. And we'll use the
DIVIDE function in the numerator,
square bracket. And we want total sales, comma. In the denominator,
square bracket, and we'll use grand overall total. And the third argument,
if we leave it empty, if our formula gets a
divide by zero error, then it will show a blank. Close parentheses and Enter. We want to add some
number formatting. Over in our pivot table, we
drag percent of grand total. And just like that, for
each row in our pivot table, the numerator gets the
correct total sales. The denominator gets 80. Now, before we jump over
to a different Power BI file with a larger data set and
look at some advanced topics, let's review what we did here. We saw how to do a Boolean
filter inside of CALCULATE to change the filter context. We saw that a
Boolean always gets converted to filter in ALL. If instead of ALL we
used VALUES, remember, ALL doesn't see the
external filter context whereas VALUES does. We saw how to use VALUES to get
a variable on the other side of a comparative operator. We talked a lot about AND,
and an AND logical test can be done with
double ampersand. It can be done in the two
different filter arguments. Or if it's the same column,
in one filter argument you can use the AND function. We saw how to do two different
columns with a filter. Remember, the CALCULATE can
see the whole data model, so we can put whichever
columns from the data model we want to filter that fact table. We saw how to do an OR logical
test with a double pipe. We saw how to use
the OR function. We talked about comparing
two columns directly. No problem. We couldn't do it with
Boolean, but we could do it with the filter function. We saw how to COUNTROWS
with the COUNTROW function. We wanted to use MIN and
MAX on the other side of a comparative operator. We couldn't do it as a Boolean,
but FILTER let us do it. And then we saw
how to use the ALL function as the remove
operator in calculate. All right. Now let's jump over
to a different file. Now, over here in this
Power BI Desktop file, which you can download, here
is a picture of the data model. We have fSales. And there's four dimension
tables-- sales rep, customer, products, and date. If we go over to
data or table view, here's our fact table date. And there's three foreign
keys-- sales rep, product ID, and customer. The only fact we have
in the fact table are the number of units sold. If we go look at
product table, there's the primary key,
product name, and we have retail price and cost, the
customer's table, the sales rep table, and the date table. If we go over to
the sales table, we can see some of our
measures, total revenue sum x over f sales related to
look up the Retail Price times the Units column. Grand overall total, well,
we did calculate it in ALL. We already calculated
percent of grand total. There it is. We have a Boolean total quarter
revenue measure right there. And we also have
average daily revenue. If we go over to Report,
we want to go down to the Overwrite tab. We already created
a product report with total revenue,
Quad revenue, and grand overall total. We have two slicers
and West is selected. Now, we want to select a very
specific cell, Quad revenue for Majestic Beaut and West. And we want to talk about what's
happening inside the CALCULATE function. Because inside the
CALCULATE we have Quad. Outside we have Majestic Beaut
on the same column as Quad, and we have the Region
column with West selected. We want to talk about
what happens inside when the CALCULATE merges the
internal and external filters using the overwrite operator. Here we have the result
from our measure. Here's the actual measure. Notice, Quad filter
on the Product column, that's the internal filter. It's listed in step
one right here. In our matrix, we have
Majestic Beaut from the Product column in the row
area, and we have west from the region slicer. Those are the external filter
contexts listed in step one here. Step two, because there's a
filter on the Product column internally and externally, the
external filter is removed. The external and
internal filter contexts become just West on
the external, just Quad on the internal. And in the final
overwrite operation, the external and
internal filter contexts get run as an AND logical test. Then the fact table is
finally filtered down to the correct numbers
and the measure can make its calculation. Now, our next topic has
to do with this measure, the grand overall total. And it has to do with
the ALL function. Now, if our goal is to remove
every single filter no matter what's happening in the external
filter context from the fact table, that's exactly
what we want to do. That measure is
perfect for calculating the grand overall total. But if we select our matrix and
come over to our fSales table, there's percent of grand total. If I click on it, I
could see total revenue divided by grand overall total. So I'm going to click and
drag down to our values area. And look at that. I thought it would
show me percentages that add up to 100%. Well, if I come up to
region and use my eraser, well, now it's working fine. And here's the problem. That measure is
always programmed to take total revenue. And since total revenue
doesn't have the ALL, it always figures out
what the total is based on whatever filters we have. But the grand
overall total, that's always going to give me
the grand overall total with all filters removed. So if I come up and select
West, well, maybe this is what I want. If my goal is to take
whatever total revenue is for each row and
the filtered total and compare it to the
grand overall total, then that measure is perfect. But if I want, in
the denominator, in my percentage formula to
have the filtered grand overall total, then instead
of using ALL, I need to use the DAX
function ALLSELECTED. Now, before we talk about how
ALLSELECTED does its magic, we already have this
measure down in our matrix. I'm going to type
ALLSELECTED and we're going to use no arguments. Close parentheses. And watch what happens to
this column when I hit Enter. There it is. There's the filtered
grand total. If I change it to East, now in
the filtered grand total cell for total revenue, it's the same
as our denominator over here in our percentage formula. The percentages add up to 100%. Now I want to change
this back because we do want to keep the
grand overall total. We want to keep our
percentage of grand total so we can compare
filtered amounts to the grand overall total. Now we'll create a new
measure with the ALLSELECTED. But first, before
we do that, we have to talk about how ALLSELECTED
got to this filter context in that grand total cell. The answer comes from
how a measure calculates in a visualization over
here in Power BI Desktop or in a cell in a pivot table. Notice that row
condition or criteria flowed into the measure. And the way it flowed
into the measure is through context transition. Just like a formula iterates
down a calculated column or through an iterator, a
measure in a pivot table sees that filter context,
which is row context, and its context transition
which pulls it in. Here, context transition
pulls the Quad in and converts it to an
equivalent filter context. So what ALLSELECTED does
when it gets to this cell is it rips away that one
row condition or criteria and jumps back before this
row context to the full filter context available in this cell. As a visualization to understand
what ALLSELECTED does, if we had row context on the
Quad product and ALLSELECTED removed it, it would jump
back to the complete column of product names. As another example,
this is the same example we just did in Power
BI Desktop, but this is in Excel, if the measure is
looking at the row context Quad and context transition
brings that Quad into the measure,
ALLSELECTED will remove the last filter created
from context transition and jump back to the
previous filter, which, for this example,
would be Carlota, Quad, and Sunshine from
the Product column and West from the Region column. ALLSELECTED removes
that row condition, and instantly it will see
all of the products, Carlota, Majestic Beaut, Quad, and
Sunshine and the region West. the full filter context
in that total revenue filtered grand total cell. Now, the reason we need to
think of this process as context transition is
because, if you try to use ALLSELECTED to get back
to the grand total cell filter context and you have
ALLSELECTED in an iterator, it will not be able to
jump back to the context outside of this row. Here's an example of using
ALLSELECTED where it can not get back to the visualization
or pivot table grand total cell. This ALLSELECTED can only
jump back to the dDate table where, in the dDate table,
there is row context. So ALLSELECTED would
remove the individual day from row context, but then
it would see the entire dDate column. So a formula like this
will not accomplish the goal of getting back to
the filtered grand total cell. And the reason is
because ALLSELECTED bumped into the
dDate iterating row by row in the
average x iterator. It removed the last filter
from context transition. That's why the true
definition of ALLSELECTED is ALLSELECTED will remove
the last filter created from context transition. But right now we want to
create two measures, one for filtered grand
total and then percent of filtered grand total. So over with f cell selected,
model a new measure, I'm going to call this
Filtered Grand Total CALCULATE, square bracket,
and we want total revenue, comma ALLSELECTED. Now, you can ignore
this description created by Microsoft
because it doesn't portray what ALLSELECTED really does. ALLSELECTED removes
the last filter generated from
context transition and jumps back to
the previous filter. Now, I'm going to close
parentheses, close parentheses, and Enter. We have our new
filtered grand total. We're going to drag
it down to our matrix. And sure enough, when
I change this to East, wow, look at that. It's cut the
filtered grand total. Now, selecting
fSales, New Measure, our measure name will be
Percent of Filtered Grand Total, equal sign,
and we want to divide with total revenue in
the numerator, comma, and our filtered grand
total in the denominator. Close parentheses and Enter. We want some percentage
number format. There's our new measure. I can drag it down
to our matrix. And that's beautiful. Now, look at this. We might want this measure,
Percent of Grand Overall Total, sitting right next to this one. Whatever I use as
a filter, now I get the correct
filtered grand total sitting in the denominator
for this percentage. And this one, it's still
got the grand overall total. Now, to illustrate another
great feature of ALLSELECTED, we have a cross-tabulated
report, sales rep, and product. And I have filtered down
to the customer, Twitter. There's the filtered
grand total. If I remove total revenue and
add the filtered grand total, is it going to work for both
the row and column headers? You bet. I can come over here
and filter to Amazon. ALLSELECTED removed
whatever the column and row headers were and got back to the
filter in the grand total cell. So yes, it will
work on a cross-tab. Over on AS2, here's
this ridiculous formula where we used ALLSELECTED. And that ALLSELECTED
is never going to make it back to that
filtered grand total cell because it bumps
into the dDate table. Over here, average
daily revenue, that measure correctly iterated
over all the days in January, got the total revenue, and
calculated the average. But right here, although
the January dates did flow into dDate-- so the iterating table
only has days for January. But forget it. ALLSELECTED removed
the row context. So when this total
revenue iterates over all of the
31 days, each cell gets the grand
total for January, which we can see right
here as total revenue. Each cell has that
grand total for January. And then, of course, average
just averages the same number. So that's how we get that
ridiculous number there. So when we're looking
at ALLSELECTED, we're looking backwards. If that CALCULATE's not
sitting and iterating in the pivot table,
then it's not going to make it back
to the pivot table. Here it's iterating and only
making it back to dDate. Over here, our filtered
grand total formula, we know that
CALCULATE's iterating through whatever the report is. So ALLSELECTED will
remove the last filter from context
transition and get back to that filtered
grand total cell. All right. Our next topic is going to
be the KEEPFILTERS function. Now, for this
example, we're going to use this file right here. On the table sheet,
we have a similar data set to the data set we used in
our last Power BI Desktop file, except for this fact table has
about 50,000 rows as compared to 3,000 in the Power BI file. Over in PowerPivot data model,
we have our tables imported. We've already created a number
of measures, Total Revenue. There's sum x,
average daily revenue. Only Quad Revenue with VALUES. We did this formula
earlier in the video. it gives us the Quad
revenue only in the row where the Quad product exists. We have just Quad revenue. That's a Boolean. We have average
yearly revenue where we're iterating over the years,
and average monthly revenue where we do a CROSSJOIN
between year and month. All of these
measures will help us illustrate the amazing
KEEPFILTERS function. In diagram view, we have
our fact table surrounded by our dimension tables. Over here in the
Excel sheet, we want to go to KF for KEEPFILTERS. We have a pivot table product. There's total revenue
and just Quad revenue. Now, remember what just Quad
revenue does with a Boolean. Because this is an
internal filter, the overwrite process
replaces the Product column. If we wanted to show
the Quad revenue only in the row where there
was a Quad, well, we did this formula
earlier, FILTER. And instead of ALL function,
we used the VALUES function. VALUES lets all of these
conditions flow in, so, of course, it simulates
an AND logical test. And the only row
that gets the revenue is Quad external
and Quad internal. Well, there's an easier
way to accomplish this than creating this formula. We can use the
KEEPFILTERS function. And all the KEEPFILTERS
function does is it instructs the
CALCULATE function to run an AND logical test rather
than the overwrite operator. That means KEEPFILTERS for every
filter inside the KEEPFILTERS function, it's going to take
this internal filter context and run it as an
AND logical test with the entire
external filter context. And KEEPFILTERS is
not a table function. Its only job is to
tell calculate, hey, I don't want you
to do overwrite. I want you to do an
AND logical test. So over here in
the measure grid, we're going to create the
measure called Just Quad Revenue KEEPFILTERS, comma. And we're going to use
our Product column, ask if it's equal to Quad. Now, if I hit Enter right
here, this is the measure grid, so there's no external
filter context. It'll give us exactly
the same thing. But as soon as I wrap
KEEPFILTERS around that Quad product, now it's going
to run an AND logical test with whatever's
inside of KEEPFILTERS and the entire external
filter context. So when I hit Enter, I
get the same thing here because there is no
external filter context. But over here in our
pivot table field list, I can drag the measure down. And that is beautiful. So if we want to run
a particular filter and run it as an
AND logical test with everything in the
external filter context, that's the way to go. If we take a closer look at
how the measure calculates in the first row, the internal
filter asks the question, are you Quad? The external filter asks the
question, are you Carlota? And, of course, the product
can't be simultaneously Quad and Carlota,
so we get a false. That means the
final filter context is a completely empty filter. Down here in the row
for the Quad product, we, of course, get
internal filter of Quad, external
filter of Quad, which, in an and
logical test, is true, so we get the total
revenue for Quad. Now, the real reason
that KEEPFILTERS as a DAX function
was invented was to deal with a much more devious
problem that pops up sometimes during the overwrite operation. And it has to do with something
called a complex filter. Now, what is a complex filter? Over here in this
pivot table, I've dragged total revenue
and a date hierarchy. And here's a complex filter. We're going to come to the
filter dropdown, uncheck everything. And for 2017, I want the
months November and December. And for the year 2018, I
want January and February. There's our complex filter. If I click OK, what
I've done is I've used the Year column
and the Month column, and there's an AND logical
test between these two columns. Not only that, but the
complete logical construction is that there's also
an OR logical test. What we're asking with
this complex filter is that we want
the sales numbers for the year 2017 and November,
or the year 2017 and December, or 2018, January,
or 2018, February. The problem that's
generated when we externally create a complex
filter like this is that the DAX
engine is going to see one, two different columns. And during the
overwrite process, if we have the Year
column inside our formula, it will actually
remove and replace it with whatever Year column's
coming from inside. Further, notice that we
selected two different items. If we had selected just 2017
and then a bunch of months, we'd never get this problem. So from a logical
construction point of view, the problem comes when we
are mixing AND logical tests with OR logical tests and we've
selected more than one item on both columns. It is the external
user of the report that takes two separate
columns and combines them in an AND logical test. This is how the DAX engine
sees those two columns. It sees Year with
2017 and '18 and Month with four different months. This is what we
are trying to do. Notice the Year and Month
columns are working together. They're not separate columns
like the DAX engine sees them. These two columns are together. And, in fact, they
create a complete table where each row means
an AND logical test. And when you go down a row,
that implies an OR logical test. Those of us from
the Excel side have lots of experience
with complex filters, for example, when we
use Advanced Filter or when we use D functions. So now that we know what
a complex filter is, how is it going
to cause problems? Well, it's not causing
problems right here. Total revenue, notice this
is the formula up here. The problem is going to only
come when one of the two or both of the columns from
the external complex filter are also inside the measure. And in particular, in iterator
functions like average x, notice there's the Year column. So in the overwrite
process, when VALUES is looking at
just 2017, well, 2017 will replace the
external Year column, but it will remove everything. And when it does
that, it actually breaks the AND logical test. Now, to see the error
in action, we're going to use this formula,
average year revenue, drag it down to the values. Now, the meaning
of this formula is to iterate over multiple years. And the only place
that's going to happen is in the grand total cell. But the grand
total cell is going to use this incorrect amount
and this incorrect amount. So we have to
figure out how this is calculating incorrectly. Well, this is an iterator
for values of the Year column right there. That means it only has 2017. We're not iterating
over anything except for a single year, 2017. But that's row context that
gets converted through context transition into filter context. That means the Year column
is internal in the measure. There's also a Year column in
the external filter context. As soon as the engine
determines year and year, this one is removed,
the year inside is kept. But wait a second. What happens when it's removed? It breaks the AND logical test. And when that happens, all four
months will appear in that cell right there. Now, it's not really that
it appears in the cell. It's the actual measure that
runs an AND logical task between the external and
internal filter context to get the final filter context
of all four months for 2017. Since average x is an iterator
iterating over only 2017, I visualize it this way. That one row has only the months
November, December, January, and February. And that's what filters the fact
table, which the total revenue measure calculates as
about 12.8 million. And then the average x,
because there's only one row, divides by one. That gives us the incorrect
answer of 12.8 million. Now, off to the
side, if you wanted to prove to yourself that those
four months total are actually 12.8 million, I just
built a little pivot table and filtered all
four months for 2017. And there it is. When four months are used
for total revenue instead of the correct November
and December, two months, this is called a complex
filter reduction error. The same thing happens
in the 2018 total row, except for this is for
four months in 2018. Then in the grand
total cell, it takes those two incorrectly
calculated 2017 and '18 numbers and averages
them to get the wrong answer. Well, how do we get
the right answer? Well, we tell that
Year column to please do an AND logical test with
the external filter context. We're going to do
our same formula but we're going to call it
Average Year Revenue, KF for KEEPFILTERS. And we need our values on dDate
of year, close parentheses. But that's what we want to
force into an AND logical test. So we wrap KEEPFILTERS around
VALUES, comma, the expression, square bracket, t
for total revenue, close parentheses, and Enter. We'll add some
number formatting. Now we can drag our new measure
down into the values area. And look at that. That is the correct
single year total or the total divided by one. And in fact, you could
highlight these two cells. Look down here. There's the sum. Specifically, here's the
external filter context. The internal filter
context, they're run as an AND logical test. And here's the final
filter context, which is only November
and December for 2017. Down here, that's
the correct average for these two correct values. Now, understanding
when to use KEEPFILTERS means we need to understand
when we can get into trouble. And to illustrate, there's no
difference between this formula here or this formula
right here if we do not create a complex filter in
the external filter context. Notice exactly the same correct
numbers all the way down. Control Z. But the thing is, when
you have a report, we're using Excel PowerPivot
and Power BI Desktop specifically because we
want to be able to filter. However, we would like
and have accurate numbers. So when you have a
complex filter based on two or more columns in
the external filter context and you're using the same
columns inside your iterator, then you gotta worry
about whether or not you need to use KEEPFILTERS. Another reason to understand
clearly what KEEPFILTERS does, over here in Power BI Desktop,
there's this New Quick Measure. Now, you better be
good at DAX if you're going to start clicking this. Because if it creates a measure
with a bunch of functions and you don't know what's
going on, I wouldn't use it. But let's try and make-- I'm going to click it. Dropdown. There it is, average
per category. The base value will pull
total revenue and, from dDate, pull the year. We're trying to make that
calculation, iterate over year, calculate total revenue, and
then calculate an average. Click OK. I could see the new
measure over here. And there it is, total
revenue average per year. Look at that. It put KEEPFILTERS around
VALUES around year, and it put CALCULATE just
in case to make sure context transition happened. But why did they do that? The designers of the automatic
DAX and Power BI Desktop wanted to make sure that no
matter what crazy filters we might externally put on, this
measure would still work. But here's the thing. It may not be necessary, just
like this CALCULATE definitely is not necessary here. So when you use the
New Quick Measure, well, it might get
you what you want. There might be some extra
stuff you don't need. CALCULATE is not needed because
we have our hidden CALCULATE on our measure. And if we absolutely
didn't have our Year column anywhere in the
external filter, then we would not need KEEPFILTERS. But there you go. At least you know, if you're
using those New Quick Measures, why they use KEEPFILTERS. Our next example is
average monthly revenue. Up here is the measure. We're going to iterate using
average x across, well, year and months together. We used CROSSJOIN, just like
we learned last video, which will take all
available years and do a Cartesian product
against all months, and then calculate
total revenue iterating, and then calculate the average. The problem here is there's
that complex filter. And in this case, both
columns are used internally in our measure. The grand total sale should
be taking one, two, three, four months and
averaging them to get average monthly grand total. But that's not what happens. Here is year and month. Context transition
brings them in. That means there's
columns internally. Well, of course they replace. And so in the grand
total cell, it definitely sees the full filter
context of everything. But CROSSJOIN takes two
years times four months and gets eight possibilities. So down here,
instead of iterating over four, its iterating
over eight different months. The answer, of
course, is KEEPFILTERS to force an AND logical test. Here's the measure we're using
to get the incorrect answer. I'm going to copy this,
Escape, click below. And up in the formula
bar, we're going to change this to KEEPFILTERS. And around CROSSJOIN, we can use
KEEPFILTERS, close parentheses. Now, these two columns will
be run as an AND logical test with the external
filter context. There it is. We're going to drag our
KEEPFILTERS down here. And that's the
correct 3.247 million instead of 3.23 million. Now, one other thing about this
is, yes, lots of times people do do formulas like this. Then we need to use KEEPFILTERS. But if we're thinking ahead,
we can avoid this whole issue by building the data model
correctly in the first place. If we go over to our data model
and over to the dDate table, our complex filter and the
external filter context use year and month, and our
formula used year and month. Well, if we want
month, why don't we just build the correct
attribute column that contains year and month? The fact that it's
a separate column, we can use it
internally in average x. And because it's
a separate column, it doesn't even mess with
those external columns in the overwrite process. So this is a data modeling
solution that's much easier. So the other way
we can solve this, copy the same formula,
Control C, Escape, down here, and I'm going to call this
that same one but DM for this is a data modeling solution. And I'm going to delete
everything in the first table argument of average
x, use VALUES, dDate. And there's my attribute column
that combines year and month. Tab, close
parentheses, and Enter. Now, over here, if I drag this
down to values, no problem. I get the same answer,
and that's that formula right there. Now, one last thing
about a complex filter. I describe it as and,
or, and multiple items selected on either
one of the columns. Now, if you want a
sure way to determine if your external filter
is a complex filter, you take the
original two columns, you multiply them in
a Cartesian product, and if the resulting rows
are the same number of rows as the original
external filter, you know you do not have
a complex filter. And it will not cause a
problem in your formula. Running the same tests on
an external complex filter and running a CROSSJOIN
when you get the result, if you count more rows than
the original external filter, then you know you
have a complex filter. Now, there's actually a
name for this type of error where we have a complex
filter and we get an error. Complex filter reduction error. So if you have a chance
of this type of error, KEEPFILTERS will
force internal filters to be run in an AND logical
test with the external filters. All right. Keep filters. Our next topic is going to be
the expanded table concept. This is an expanded
table diagram. This diagram represents
a data model. This is a star schema
data model, fact table, dimension tables with one to
many relationships feeding into the fTransaction table. Now, in an expanded
table diagram, it portrays what's going on
here but in a different way. The table names are
listed across the top. The column names are
listed down the side where the yellow area
represent original columns from the table. Down here, these are
the original columns from the fTransaction. And the blue area
represent columns from other tables that feed
into this particular table through a one to
many relationship. Now, this is a star
schema data model. If we look at a slightly
different data model, this has a snowflake where we
have region feeding from one to many into dSales rep. DSales rep is feeding into
one to many the fTransaction. We still read the
cross-tab expanded table diagram the same way. The yellow columns represent
the original columns from the table,
and the blue ones are external columns
from other tables that feed in through a
one to many relationship. Now, we're going to be using
star schema data models, so this is what our expanded
table diagram will look like. And there's one, two, three
uses for this expanded table diagram. The first one is we can ask,
which tables will a column filter affect? So for example, if we use
the ALL function on units, notice there's only one table
that that filter would affect, fTransactions. But if we use the same ALL
on the Product column, here's Product, we can clearly see
one, two different tables will be affected. The second thing
that this diagram can help us with is we
can ask the question, which columns will a
table filter affect? So for example, when
we use the ALL function on the fTransaction
table, now we have a complete list
of every single column. We can clearly see
that when we used ALL, it removed every single filter
from every single column in the data model. In just a moment,
we'll also see that we can use, without a
function wrapped around it, a full table filter. And that will allow us to go
backwards across a many to one relationship. So for example, if I have a
particular product in the row area of a pivot
table, it is going to filter the fTransactions. But if the goal of my
measure is to count how many unique months we sold
that product, look at this. I need the product filter to
filter the fTransactions table, which, of course, it
does through the one to many relationship. But then I want it to go
backwards across a many to one. No problem. We can do that. If the measure is
counting distinct months, I just use a full table filter. And because this expanded
table has all those columns, product from the row
area filters this table. And when that table
is filtered down to just that product,
well, of course, month is in this expanded table,
so it also gets filtered down, and our measure will be able
to count the distinct months for a particular product. The third thing we
can do is we can see which expanded
table columns are in play with any table filter. So if I use
fTransaction, in fact, let's-- here's the
example we'll do. We'll use not the ALL
function but ALLEXCEPT. That means we can
remove the filters from everything in a table
except a particular column. And look at this. We're allowed, because
this is the expanded table, to put the full
fTransaction table and then exclude one of the
expanded columns, the dDate. Now, to see a couple
examples of table filters and how this diagram can
help explain what's going on, let's jump over to a
different Excel workbook. This is the Excel file
we're going to use. Here's three tables,
date, dProduct. Those are dimension tables. Here's the fTransactions. In diagram view, we
can see fTransactions. And there's our two
dimension tables many to one. And we already have
three measures. The first one is a simple sum. Here's our first pivot
table product in the row. From this table right
here, we have total sales. And, of course, total sales. This product flows in,
filters the fact table, and we get our total
sales for that product. If we look at our
expanded table diagram, here's date, product,
and fTransactions. Off to the left, these
are the column names. FTransaction, since this is
a star schema data model, has every single column
in the data model. So the way we can use the
expanded diagram is this, is a complete list
of all the columns that can filter the
fTransactions table. So, of course, over
here on the pivot table, Carlota is from
the Product column. We can see the Product
column drawing a line across. Well, it filters dProduct. And, of course, it's going
to filter fTransactions. Now, a number of times
in this class, our goal was to create a
measure that calculates the grand overall
total no matter what filters from the external
filter context might flow in. Well, the way we do that
is we use a table filter. And specifically, we put the ALL
function around fTransactions. That will remove every single
filter in the data model. Over here in the
measured grid, we're going to create
grand overall total. There's CALCULATE to change the
filter context for total sales. Comma, ALL function, and
here's our complete fact table, fTransactions. Close parentheses. Close parentheses. Now, we already
did this formula, but now we can clearly think
about this in a different way. Because we know the
expanded table diagram shows fTransactions with all columns,
all filters in the data model will be removed. It doesn't matter what column we
drop into the row area or slice or we use. This will always give us
the grand overall total. Over here in the pivot table
field list, there it is. I drag it down to values, and
we get the grand overall total in every single cell. Here's a date filter. If I select 1/19, of
course, total sales is filtered by both a product
column and date column but not grand overall total. The ALL function wrapped
around that fTransactions removed all the filters. What if we use the ALL function
just on the product column? It would block this
filter but not this one. Now, I'm going to unfilter this. This measure will be called
ALL Works on the Product Column Only. There's our expression, comma. We're going to use ALL,
but this time we're going to choose a single column. So we're only removing
whatever filters might be placed externally
on the product column. Close parentheses, close
parentheses, and Enter. Over here we can drag ALL Works
on the Product Column Only, and there we go. We get 950. The ALL on the product blocks
the product from the row area. If I select a date, whereas
total sales is affected by both product and date,
ALL Works on Product Column Only is only affected
by the date column. OK. So we saw, using the ALL
function, a table filter and a column filter. Now we want to talk about
using a full table filter but not inside any function. And our goal is
going to be this. For every product,
I want to count the unique number of months
that we sold that product. So for example, Carlota,
if we did this manually, I can see we only
sold it in one month. And for Quad, well, we sold
it in two distinct or unique months. Well, the month column
is in the dDate table, so we're going to
have a measure. And there is an awesome DAX
function called DISTINCTCOUNT. We'll use it on this column. But the problem is that
is on the date table. Our filter's coming from product
and going to fTransaction. So how do we get the
filter to go backwards? Table filter. Over in the data
model, I've already created this measure,
distinct months. And there it is, DISTINCTCOUNT. We just point it to
the month column. Over here in the
pivot table, I'm going to drag distinct
months down for each product. The problem is it's
counting the entire column. And since there's
only two months, we get two months everywhere. All we have to do is take
that distinct month's measure, put it inside of
CALCULATE, which will change the filter context,
and use our table filter. There's our new measure,
Count Distinct Months Product was Sold, square bracket. There's our distinct month's
measure, Tab, comma, ft, Tab, the full table. Notice there's no ALL around it,
so any conditions or criteria that flow in from the
external filter context will filter that table. Close parentheses and Enter. Now, over here we can drag our
new measure down to the values. And this is amazing. Now, actually, I've been
adding some wrap text, so I'm to do that here. Home, wrap text. And there it is. We can tell one unique
month we sold Carlota, two months we sold Quad. Now, taking a closer look
at this measure in the Quad row of this pivot
table, before we drop the measure
in the pivot table, looking at the
underlying data model, no columns are
filtered anywhere. That Quad filter flows
into the measure. If we're looking
in diagram view, that Quad product filter
flows across the one to many relationship over
to the fTransaction table. If we're looking at the
expanded table diagram, the product column
definitely is in that table. So when we use that
condition or criteria, it filters the fact table. Looking at the actual
underlying data model, the Quad filter flows
across the relationship and filters the
fTransaction table. But as soon as it tries
to flow this direction, it bumps into the many side. So to get it to
flow across, we're going to use a table filter. Once we bring our table
filter to our measure, then the full expanded
table fTransaction is in play, because
product and month are both in that expanded table. When the product filter
flows the transaction table and actually filters
that table, it flows backwards to
the Month column in the date table, which
is now filtered just for that Quad product. So the product table
is filtered to Quad. It naturally flows from
the one to many side, filters the fact table. Now that we have
our table filter, Quad will flow
across one to many and filter the dDate table. Then DISTINCTCOUNT can do
its job on the Month column. So the question, how many
unique months did we sell Quad, the answer is two, all because
we have a table filter. Now, for our last example of
the expanded table diagram and table filters, if it's
true that fTransaction really has all these
columns, we should be able to go over to DAX
Studio and visualize and see this table. I don't know how to do that. But when we use the fTransaction
table in any DAX formula, all these columns
are really there. And in fact, you can
access these columns. So for example, if
we use the ALLEXCEPT, I'll put the fTransaction
and ALLEXCEPT will not remove all the filters
from fTransactions. It'll remove all of
them except for any ones you list in its arguments. And you're not going
to believe this. We can list a column
from a different table that is in the expanded table. To illustrate the use
of expanded columns, we have two measures already
over here in the measure grid, DISTINCTCOUNT on the month--
we've already used that-- and I created Remove
Everything with ALL. We're pointing to that
distinct month's measure, and I'm removing everything
using ALL on the fTransaction. Here's our pivot table. I'm going to drag
distinct months and remove everything with ALL. Now, it's a silly formula,
but we can clearly see distinct months is counting
February and January 1. And then with ALL,
it removes this because we use the
fTransaction table month, so it sees the
whole column and it gets a count of two everywhere. But you're not going
to believe this. We can actually remove
everything in the data model except for a month. And how do we remove
everything in the data model? Well, one easy and fast way
is to use the fact table. And if we remember that a fact
table has an expanded table equivalent with
all the columns, we can just use ALLEXCEPT and
exclude the Month column. So over here in
the measure grid, I'm actually going to copy this,
Escape, formula bar, Control V. We don't want to remove
everything with ALL. We want to remove everything
except for the month. So instead of ALL, ALLEXCEPT. And ALLEXCEPT has first
argument, table name, comma. And this is so crazy. We can use a column not from
the original but, down arrow, from one of the
dimension tables. Because dDate month is
listed in the expanded table, when I hit Enter,
ALLEXCEPT just did its job. It removed everything
in the data model except for the month. That means this measure
should be working again. Back over to Excel. Here's our measure. Drag it down. And look at that. We did just what we
want, remove everything except for the month. So the expanded table diagram
can help us in three ways. First, it can help us figure
out which one of the tables will be affected
by a column filter. So for example, if I choose
the product column to filter, I draw a line, it will affect
product and fTransactions. The second way that
this diagram can help us is that it can tell
us which columns will be affected by a table filter. We can clearly see in
the expanded table here, every single column
will be affected when we use fTransaction, either
inside of something like ALL, where we remove all the
filters, or when we use it as a table filter to go
backwards across a many to one relationship. And then finally,
being able to see all the columns in
an expanded table means you can use
any of these columns when you build a DAX formula. Now, for our last
example about how to use CALCULATE to
change the filter context, we're going to use this
Excel workbook here. I've already built a
pivot table, year, month, total revenue. And our goal is to calculate
last year's sales, last month's sales, and then calculate
year-over-year percentage change. Over here in the
measured grid, we're going to calculate
last year revenue. Calculate total revenue. And in the filter argument,
we're going to change the filter context with the
Time Intelligence function-- and this is one of the
coolest functions in DAX-- same period last year. Now, all it needs is
ddDate, the unique list of dates from the date table. Now, Time Intelligence
functions require that you have a date table. They do not work unless
you have a date table. So we use the date,
close parentheses. And here's what this
amazing function does. It sees the external
filter context, whether it's a day,
a month, or a year, and it jumps back,
gets the correct dates, and delivers them as a valid
list of dates to calculate. Those dates filter
the fact table, and then total
revenue is calculated. Close parentheses and Enter. Now we can drag our
measure down to the values. And look at that. It got the correct
amount for last January. Here it got the correct
amount for last year. The grand total
cell is incorrect, but we're not going
to use this column in our final pivot table. In our final percentage
change formula, we'll fix that grand total cell. Now, for last month
revenue, we're using CALCULATE total revenue. And to change the
filter context, we'll use another Time
Intelligence function. This is my second
favorite, DATEADD. If you know Excel, this is like
a super charged EDATE function. The first argument
dates we put are date column, comma, number of
intervals, forwards, backwards. We're going backwards, so I
want to go back one month. And here's how the DATEADD
is like a super charged EDATE function in Excel. We can do day or we can jump
back month, quarter, or year. If you remember
the EDATE in Excel, we can only jump back a
certain number of months, or back or forward a
certain amount of months. So I'm going to
type month, and that will do a close parentheses,
close parentheses. DATEADD, we'll see the
current filter context, take those dates,
jump back a month, and deliver a valid list
of dates for calculate. And I hit Enter,
here's our new measure. We drag it down to
values, and there we go, last month, last month. Now, we don't want
anything in the Year row, because we don't
have any last year. And we don't want anything
in the grand total. So we're going to have
to amend this formula. And the way we're
going to do that is I'm going to ask the
question, hey, Month column, are you filtered? True. True. There will be a false here
because all the months are present. Same in grand total. Here's our measure. Is Month filtered? Is filtered. And we're going to use ddMonth,
close parentheses, and Enter. To see how it works, we'll
drag it down to the values. This is not something that we
would use in a final report, but there it is. Are you filtered for the month? False. False, all the way
down to grand total. If is filtered dd,
there's our month. Close parentheses. If it's true, then please
give me the formula. Otherwise, when we leave
this argument out, an if, it will put in a blank. close parentheses and Enter. Now, if we go over
to our pivot table, that is looking beautiful. We don't need this
one, so I uncheck. Now, our next measure
is going to be comparing total revenue and
last year revenue and figure out the percentage
change from one period to the next. So right here, I need to
compare the difference between these two to whatever
last year revenue was. Down here in the
Year row, I need to figure out the difference
and compare it to last year revenue for percentage change. Now, before we jump
over to the data model and create our measure,
I want to think about how we would actually
make this percentage change calculation in an
Excel spreadsheet as compared to a DAX measure. Now, in an Excel Spreadsheet,
if I'm creating a formula here, I can refer to this cell
right here and any other cell in the column. For DAX, we can't actually
jump back a few cells. We have to do something like
calculate the actual amount as a separate formula. So when we get to calculating
the percentage change for February, the
measure is going to have to have total revenue
and a separate measure for last year revenue. But with Excel, if I
have a single column, I can refer to
February and February from the previous year. So the formula for
percentage change, this year's amount minus
last year's amount, close parentheses,
divide by and compare it to last year's
amount, and Enter. And not only that,
but I can certainly copy this formula down. So there it is, 1.9% change
between November last year and this year minus 5%
from last year, February, to this February. In DAX, we actually had
to create this measure to bring last year's
amount down to the same row or cell on the pivot
table, so this formula, this year minus last year, and
we compare it to last year. So for people coming
from Excel, sometime we are used to being able to
refer to anything everywhere in our spreadsheet. In DAX, it doesn't
always work that way. Now, let's go over and
create our measure. Now, over in the
measure grid, we'll call this Percentage Change. And we're going to
start with divide. In the numerator, I
take this year's sales and I subtract last
year's revenue. Numerator, comma,
and we're going to compare it to last period's
amount, last year revenue, close parentheses, and Enter. We'll add some percentage
number formatting. There it is. we'll
drag it down to values. And look at that. We have our correct
percentages all the way down to the grand total. We need to turn that off there
because that's not correct. Also, we want to
notice something. There's nothing showing up here. If we look back
up at our measure, the third argument of divide is
if the denominator, last year revenue, is equal to zero, then
you get a divide by zero error. But this function DIVIDE is
programmed, if that's the case, to put a blank in. Now, the way we're going to
turn off the grand total cell is we're going to think
about the Year column. The Year column has
one value everywhere. For all of these months
it's 2007, for the total, it's 2007, and so on all the
way down to the grand total. The grand total is the only cell
where there are multiple years. So we're going to ask the
logical question, hey, Year column, do you have one value? All of these will be true. The only one that won't
will be the grand total. So up in the formula bar, the if
function, and then HASONEVALUE. HASONEVALUE is
oftentimes the function you want to use when you want a
false in the grand total cell. DD down to year, close
parentheses, comma. So in logical test,
true everywhere except for the grand total cell. So then we run this. And just like DIVIDE,
the IFs, third argument, will put a blank in
when there are errors. So DIVIDE and IF
over here in DAX do some things that we
can't do over in Excel. Close parentheses. And by the way, the blank-- and we mentioned this earlier-- is not like a double
quote, double quote, zero length text string
that we use in Excel. That's actually considered text. When we use the BLANK
function, or use the third argument in IF, or
the third argument in DIVIDE, it actually puts blank in,
which is like an empty cell or a null value. It is not a zero
length text string. So when I hit Enter, over here
I see blank because, of course, there is no external
filter context, so it's seeing all the years. Over in our pivot table, there
it is, blank, and also blank up here. Here's page 39 from our
PDF notes, the DAX Formula Evaluation Context Summary. Remember, there's two
evaluation contexts, row context and filter context. The CALCULATE and CALCULATETABLE
functions do two things. They can change
the filter context and perform context transition. Remember, all measures have
a hidden CALCULATE function wrapped around them. There are two types
of filter contexts before we get to the final
filter context, external filter context and internal
filter context. And when those two are merged
into the final filter context, three operators are used-- intersect OR/AND logical
tests, the overwrite operation, and the remove operator. And when considering when
to use the KEEPFILTERS, we have to look out for
complex filters that exist in the external filter context. And the same columns are
used in the first argument of an iterator. ALLSELECTED, remember, that
removes the last filter generated from
context transition. And when the last filter
generated by context transition is the row context
in a pivot table, then ALLSELECTED can help to
calculate the correct filtered grand total amount. And remember, column filters
work on just columns, and table filters work
on the expanded table and can go backwards across
a one to many relationship. Wow. That was an epic video
all about the CALCULATE and CALCULATETABLE DAX
function, filter context, and, of course, ALLSELECTED
and KEEPFILTERS, too, in both Power BI
Desktop and Excel PowerPivot. Now, if you like that video, be
sure to click that thumbs up, leave a comment, and subscribe,
because there's always lots more videos to
come from excelisfun. And we'll see you next video. If you like what you
see in this video, click that subscribe button
and the bell icon to get notified about new videos.
