Welcome to Excel Magic Trick number 1437.
Hey, if you want to download this Excel workbook,
either the Start file or Finish file,
so you could follow along, click on the link below the video.
Hey, we've got a great video here.
We're going to create a DAX query that
runs an OR logical test using the IN operator.
And we're going to pull sales team data to our Excel sheet
from the data model.
Now here's a picture.
Here's our data model.
We have almost 1.9 million records here.
And we have a dimension or lookup
table with our sales rep.
We're going to use this DAX query code to pull
the data into our Excel sheet.
Now, we have one, two, three, four teams.
And we want to put any one of those teams
into our Excel table in the Excel spreadsheet,
refresh, and instantly have the team's data from the data model
show up in our Excel sheet.
Now let's go over to the sheet DAX query.
Here's our teams.
We'll use these names as inputs later.
Now let's go look at the data model.
There's our sales table with 1.9 million records.
Sales rep.
There is a relationship we can see over here.
We need to add our OR criteria as a table.
So I'm going to Alt-Tab.
I've already converted it to an Excel table using Insert Table.
And of course, I've named it, DIS team names.
So I'm going to click in one cell in that table.
PowerPivot, add to data model.
And there it is.
DIS, because it's disconnected.
It's not going to have a relationship.
But we're going to access that single column that
contains the names for our OR logical test.
Now let's go back over to Excel.
We're going to start this out by clicking in a cell,
going up to data.
And we want to establish a connection to the data model.
So we use existing connections.
On the Tables tab, we can connect
to any one of the tables we have in the data model.
And then once we build our DAX query,
it doesn't matter which table we start with, it'll pull whatever
we ask for from the data model.
So I'm actually going to start by just selecting the smallest
table.
I could not select all sales table
because that table wouldn't fit into the Excel sheet.
I'm going to click Open.
There's our import data.
I'm going to click OK.
Now after we write our DAX code, we're
going to come to our table and right
click Table, and use Edit DAX.
And we'll put our DAX code here.
But instead of writing it in that dialog box,
we're going to go up to Add-Ins and we're
going to use DAX Studio.
Now DAX Studio, if you don't have it, you just
search Google, download it.
It's super easy to download and install.
When we click this button, because we already
have our Excel workbook open, it says, hey,
do you want to connect to this Excel workbook?
We do.
I'm going to click Connect.
Now over here in DAX Studio, on the left,
we can see each one of our tables.
We can click the triangle to expose the columns.
Now we're going to pull a table of data.
I'm first going to click over here, Control,
and roll my wheel to zoom in.
And I'm going to type Ev, and I see the evaluate command.
And I hit Tab.
EVALUATE, Enter.
Now we can create our table function formula.
We're going to use CALCULATETABLE.
CALCULATETABLE will allow us to start with a table
and add filters from any one of the other tables
or this particular table that we start with in our data model.
So the table we want to pull data from is the All table.
So I'm going to double click.
And boom, there it puts it in.
Comma.
The second argument is going to be our OR logical test.
Now we're asking a question of the Sales Rep
column in the dimension or lookup table.
So I'm going to double click that.
Now instead of writing equals in double quotes and then the name
and then using the OR operator or the OR logical function,
I'm not going to type an equals sign.
I'm going to type a space and then the IN operator.
I don't even see it here in my drop down,
but I'm going to type a space, and that will work.
Now if you know IN in SQL, it's the same here.
Whatever criteria follows will be used in an OR logical test.
I'm going to type Enter.
And I want to access the criteria
from my disconnected team names or criteria column.
So I'm going to use the Values function.
The Values function will get a unique list from a column.
Now ours is already a unique list,
so I'm going to double click.
But that will allow us to materialize this table
with allowable names.
And we'll look at it, search through the Sales Rep column,
filter the D product table down just
to the names that match the OR criteria.
And then, because we're using CALCULATETABLE,
that filter will flow across the relationship
and filter the fact table down to just the records we want.
Close parentheses.
And that's it.
We can run this.
And there's our result. Now it took a while to run over here.
But once we've put this into our existing connections edit DAX,
we can simply refresh two of the tables we want,
and it will refresh quickly.
Now I'm going to highlight all that.
Control-C, Alt-Tab.
Now I'm going to come to our established connection,
right click, Table, Edit DAX.
We're going to make sure command type is DAX.
Then I'm going to highlight it, and in the expression,
Control-V. Now I click OK.
And there's our result. I can Control, Down arrow.
So we've got a bunch of records there.
Sheila Dawn, Dean, Missy, Control, Home.
And then we could do whatever analysis we want.
Now let's test this out.
Team number 3, I'm simply going to highlight,
Paste into our table, Control-V. I do not
want to go to Data, Refresh All, because this is connected
to a large data source and it would take a long time
to refresh, pulling everything in.
So I'm going to open up Queries and Connection.
Queries and Connection, those are the original queries
to an external source.
Those are pulling into the data model.
But I want to come over to Connections.
This linked table query, that's this query right here.
I'm simply going to come right click, Refresh.
And just like that, our table has refreshed.
Let's try it again.
Tyrone, Kiki, and Pop are our sales rep for this team.
Control-V, right click, Refresh.
It's working.
And there we go.
All right.
That was a little bit about how to use
the IN operator and access values in our Excel
spreadsheet for an OR logical test to pull data
from the data model.
All right.
We'll see you next video.