Welcome to Excel Magic Trick number 1,440.
Hey, if you want to download this Excel workbook,
either the finished file or start
file so you can follow along, click on the link
below the video.
Hey, we've got to talk more about the DAX IN operator,
but this time we're going to see how
to use the IN operator on two columns to combine
and AND & OR logical test directly in our measure.
Now here's our table of data, and here's our logical test
to determine which sales numbers to include
in our summing calculation.
Now IN operator is fundamentally an OR operation,
an OR logical test.
But when we use two columns, we'll
be able to combine the first part of the OR, which
is an AND logical test, with the second part, which will
be a second AND logical test.
So for a record to be included, we
have to find the region East AND the Channel is Web Site,
or the region is West and the Channel is Web Site.
Now internally, in our measure we'll
have this logical construction, but when we drop it
into a pivot table, of course, the row area criteria
will be added as an additional criteria.
So that calculation right there is actually,
if we look over here, we found a product Quad and the region
West and the Channel Web Site, and then
down here, Quad and East and Web Site.
So both of these numbers will be included in that total right
there.
Now here's the DAX formula we're going to use,
and in order to get the IN operator to work
on multiple columns, we have to use this construction putting
the two columns inside of parentheses,
and then the IN operator, there's one OR,
there's the other OR.
So it's these two conditions OR these two conditions.
Now also in CALCULATE, the filter argument cannot handle
multiple columns.
So any time we have multiple columns,
we then use the filter function.
All right, now if we look down here,
this is another way to do this.
And I will do this formula at the end
and you can decide for yourself whether you
like using the IN operator or the OR with a double ampersand
for the AND logical test.
All right, this is the finished file,
let's go over to the start file.
Now over here in the start file, here's our table.
I've already converted this to an Excel table
by going up to Insert, clicking on Table button or Control-T,
and I want ahead in Table Tools Design, named it fSales.
I've also already imported it into the data model.
Now if you do not have Power Pivot,
go back and watch Excel Magic Trick 1439,
because if you have any version of Excel 2016,
you can import tables into the data model
and build DAX formulas.
So go back and watch that one if you don't.
Hey, in this video, I'm going to click here.
I have Power Pivot, so I can click on Manage Data Model.
I've already imported the table and there's just
one single table.
Now I could come back to Data view
and build the formula here down in the measured grid,
but guess what?
We're going to go to Add-ins and we're
going to use DAX Studio to build our formula,
and then we'll copy it from there
and paste it into our data model.
Now if you don't have DAX Studio,
just go search Google for DAX Studio and download it,
it's a free add-in.
So I'm going to click DAX Studio.
Now because I had Excel open, it assumes
I want the open Excel workbook's data model.
By the way, if you have Power BI Desktop,
you would click this button and browse to the correct folder.
I'm going to say Connect.
Now here's DAX Studio.
Before we start building our formula,
I want to go look at our formula.
CALCULATE is going to alter the filter context.
Here's an aggregate function that
would give us all of the sales.
If we look inside our pivot table
right now, if we just drag the SUM,
it would get the total for Bellen, Carlota to Quad.
But CALCULATE will alter the filter context,
and look at this, we're going to use FILTER.
Yes, we have our OR logical test using the IN operator.
But guess what?
There's two columns here.
So that means we're going to have
to use FILTER to get calculate to work on two columns.
Not only that, but FILTER is going
to need a table to iterate over to find which rows are
valid for this OR logical test.
And we're not going to use the whole fSales table,
we're going to use just two columns, the two
columns we're interested in for our OR logical test.
Now the way I want to build this over and DAX Studio is I
want to build the ALL function first,
see what it evaluates to, then the FILTER,
see what it evaluates to, and then put
that inside of CALCULATE.
All right, so over in DAX Studio we can build our formula here.
Not only that, but we can see our table
and all of our columns.
I'm going to click on the triangle to expose the columns.
Now let's click right here, I'm going
to use control and roll to zoom in.
Now we're going to have to use the EVALUATE command, Tab,
in order to see the ALL and FILTER tables that
are generated in our DAX code.
Now I going to start with ALL.
Now ALL is a function that if we gave it the whole table,
would just materialize the entire table all records.
But if we give ALL just a particular column--
and I'm going to Down Arrow to Region and Tab--
ALL will generate a unique list of items from that column.
But watch this, comma, and this time I'm not going to type out,
I'm going to come over and double-click Channel--
I love DAX Studio, look at that.
It puts in the table and the field name in square brackets.
Now if I close parentheses on ALL, when you put two columns,
it will give you a unique list of records from those two
columns.
Now let's evaluate this and see down here
what filter we'll be iterating over.
So when I click Run, there it is,
it's a unique list of all the possibilities
from our fSales Region and Channel.
Now why do we use ALL inside of FILTER instead of just fSales?
Because that means the FILTER function
will be iterating-- that means calculating
for each row over a much smaller table.
Now for our data set it doesn't matter at all in terms
of performance, but for a large data sets, it does matter.
An efficient way to build this formula,
regardless of whether it is a small or big data set.
Now let's put this inside a FILTER.
F-I, Tab, and FILTER needs a table in its first argument,
ALL delivers a table.
Now we come to the end, comma, and we
need to filter that table to show just
West Web Site, East Web Site.
All right, now I'm going to hit Enter.
So the second argument of FILTER.
And this is an OR logical test and we're
operating on two columns.
I'm going to put the two columns in parentheses.
So I come over, Region, double-click, comma, Channel,
double-click, close parentheses.
So that's the two columns for IN, space.
And space.
And now we have to use curly brackets,
and since we have two conditions on two columns,
we're going to use row constructor syntax here--
open parentheses, and now we can IN, double quotes, do West,
and double quotes-- that's from the first column--
Region, comma, and then the second condition,
IN, double quotes, Web Site.
And double quote, close parentheses.
Now I'm going to come right after IN here and Enter.
Later we'll use that button to format it.
So that is an AND logical test, and it's the first part
of our OR logical test.
Now I put a comma and watch this--
I'm going to cheat.
I'm going to highlight all of this, Control-C, Control-V,
double-click West and type East.
So now if I come to the end, close curly brackets.
Now this whole little bit here is
called the table constructor.
We have our curly brackets and a comma.
So that's one row in the table, that's another row.
This little bit right here is called a row constructor.
So altogether, our mini-table defines our OR criteria for IN.
IN will know what to do now.
It wants to find any record in this table that's West
and Web Site or East and Web Site.
Now when I click Run, yes, I get an error
because I wasn't paying attention to the yellow prompt.
I need to come to the end, close parentheses,
and now when I see my yellow prompt
and I'm paying attention, now I can click Run.
And there it is.
That is what FILTER delivers as valid criteria
to calculate so the SUM function can calculate with the filter
context coming from the pivot table
and join together with this criteria.
Notice if you're visualizing the pivot table
and it was the Quad row, actually,
these two conditions would be added
with the quad as a 1, 2, 3 AND logical test,
and down here, 1, 2, and the Quad would be the third.
ALL AND logical test.
So any record that has West and Web Site and Quad or East
and Web Site and Quad.
Now let's come up here, and we can put this inside of C--
I see my CALCULATE, Tab, in the expression.
Actually I'm going to hit Enter.
In the expression here, S-U-M, open parentheses,
I can double-click the Sales column and there it is--
close parentheses.
Now comma.
So there is the first argument of CALCULATE.
CALCULATE will take this condition,
merge it with the pivot table condition, and boom.
Now I'm paying attention, I see the yellow parentheses,
close parentheses.
Now, guess what?
EVALUATE will not work on CALCULATE
because it's a measure.
But no problem, here's a trick I learned from Marco Russo.
We're going to use the ROW function--
and this is just temporary because ROW--
actually, if I Backspace, ROW, and if you select ROW,
you can see-- returns a single row table with columns
specified by a DAX expression.
So Tab.
And the name, that's the name of the column.
I'm just going to use something like X, comma.
And then I come to the end, close parentheses.
Now I can evaluate this because that's a one-row table,
I come up here, and there's our total answer.
Now we do not want the row over in Power Pivot,
so I'm simply going to highlight the CALCULATE part of this,
Control-C, we'll go back over to our start file.
Now there's our started pivot table.
We can go to Power Pivot, back to the Data Model.
In the measure grid, we can create our formula.
We can also come up to Calculations,
right-click, New Measure.
Or if you don't have Power Pivot, simply right-click,
Add Measure.
The name of the measure, something
like East-West Web Sales.
Click down here, Control-V.
Now I can add some no formatting.
Two decimals, click OK, and there is our formula.
I'm going to drag it down to Values, and just like that,
we used IN to do an OR logical test on two columns.
Now I want to go back over to DAX Studio,
and we want to try this with the OR function instead
of the IN operator.
I'm going to type OR open parentheses,
and I can build my two logical tests.
Delete, Delete, Enter.
The first test is, hey, Region, are you equal to East?
Then a space, and then double ampersand
to instruct our formula to do an AND logical test,
Space, Delete, Delete, Enter.
And the channel will have to be equal to Web
Site, end double quotes, comma.
Now I'm going to come right to the beginning right there
and hit Enter.
So we have our comma.
Guess what?
I'm just going to copy this AND logical test, which
is the first part of our OR logical test, Copy,
come to the end, Enter, Control-V and double-click
East, and type West.
Now notice earlier, we did West first and then East,
of course, with an OR or AND logical test.
It doesn't matter the order in which you test,
it's all about how the truths and falsehoods come out.
Now I'm going to close parentheses on the OR.
Control-Shift End to highlight all the way to the end, Delete.
I see my yellow prompt.
Close parentheses on FILTER, close parentheses on CALCULATE,
close parentheses on ROW.
Now I can click Run, and there it
is, I get exactly the same answer.
Now before we copy and paste this over in Power Pivot,
we can format it, and actually, we
didn't format the first formula.
But here, if I come up to Format Query, oh wait a second,
it's going to do the Row X and I don't want that.
Control-Z to undo over here in DAX Studio.
I'm simply going to highlight that first part, Delete,
and then get rid of one of these parentheses over here.
Now I can click Format Query, and there's our formatted code.
Now sometimes you bring your measure straight over here
and you want to just format it, if you
want to include the name, then you
would type the name, colon, equal sign, Delete,
and then you can hit your Format Query and there it is.
Now I'm going to click here, Control-A, Control-C, Alt-Tab,
come over to pivot table, you'll just right-click the table,
Add Measure.
Measure Name, Control-V, Backspace a few.
Come down here, Control-V, and I'm not
going to include this little title up here.
Delete, Delete.
Currency, two decimals, click OK.
Actually, I don't like that name, right-click Edit, Site,
and then O2, click OK.
Now we have our second measure, I can drag it down here
and we get exactly the same answer.
All right, that was a little bit of fun
with an OR logical test on two columns.
We saw how to do it with IN, we saw how to do it with OR.
In the comments section, tell me which one do you like better
or which one do you prefer.
All right, we'll see you next video.