Instructor: Microsoft just dropped
the November, 2023 updates for Power BI
and there's some really cool stuff in here.
And one of the things that I really wanna check out
is this brand new DAX query view.
All right, so this DAX query view,
it's a brand new fourth view that's currently in preview.
So if you're not seeing it yet, make sure
that you're running the November version
and have turned on this preview feature.
But basically, the DAX query view allows you
to use powerful DAX query expressions
and keywords to explore, analyze
and view data in your model without needing
to build visualizations, which is seriously cool.
So there are a few things
to keep in mind with this new feature.
First up, all DAX queries need to return a table.
So you just can't write a measure expression
that returns a scale our value and expect it to work.
Second, to create a query,
the only required keyword is evaluate.
And while evaluate is the only required keyword,
there are actually a bunch of other keywords
that can be used to return data, like order by
start at, define, measure,
VAR for variable, table or column.
So depending on the type of data that you're looking
to return and how you want it ordered or structured,
you're gonna use a combination of these different keywords.
Now if you're looking to explore measures,
then you're gonna want to use the measure keyword
in conjunction with define,
and that'll help you do just that.
So we're gonna jump over to Power BI
and let's take a look at this in action.
All right, so we're in this new DAX query view
and the interface looks very familiar.
We've got our home, help and external tool tabs here
from our home menu.
We've got some different and new options here.
So we can format the query,
comment and uncomment, find, replace.
And then we have this command palette.
Over on the right-hand side here, we've got our data pane
with their table view and then the new semantic model view.
In the center, we have our query and query results windows.
So we'll type in our DAX queries up top
and then we'll see the results window down at the bottom.
And then we have additional tabs.
So anytime we create a new query
or run a new query, we'll create a new tab
down here at the bottom.
So all in all, a super familiar layout.
Now let's take a look at our first example here.
When I first opened up Power BI,
this was populated by default.
So if we click run, we see
that we are evaluating the top 100 rows
of our calendar table.
So we can look at the result here.
We've got our date start a year, start of month,
and then start of week columns for the first 100 rows.
So what happens if we wanna explore
our orders table a little bit?
So there's actually this brand new feature
called Quick Queries that if you right click the header
and come up to quick queries, we can show the top 100.
So similar to that top end function we just used, right?
And that returns the top 100 records of our orders table.
And this is ordered by the order ID it's sending.
So that's how it's determining that.
The other quick query option that we have
and right-click come up is our column statistics.
And this is pretty cool
because it actually creates kind of some column profiling
and statistics of your data tables right here
in kind of the front end of Power BI.
So while the query editor, data profiling
and QA tools are phenomenal, it's actually kinda nice
to be able to have this right here
in the front end of Power BI.
So you can see we generate this brand new table.
We have things like counts and distinct count,
min, max, medians, means, standard deviations,
odds, evens, quartiles
range in days, range in months and so on.
So we've got a bunch of different statistics
about the columns within our table.
So this is great, but what if we wanted
to create our own query to return a table?
So if we add a new query
and let's just evaluate our orders fact table.
Click Run,
and we get all of the columns from the order fact table.
Again, this is pretty cool, pretty straightforward.
Again, this isn't giving us anything different
than we couldn't get from our table view here,
but what if we wanted to see the individual customers
and their corresponding order volume, right?
So we have all of our customers here,
but each record represents an order, an individual order.
So what if we wanted to kinda aggregate this
or group it?
So we can come back to our query here
and we're gonna add in a summarized columns function.
And what we want to do here is we want
to summarize the columns
and we're gonna base this on the customer ID.
And we're gonna create a new column for orders
where we're gonna sum the order quantity
for every record within the table.
So we're gonna sum this for our fact table
and then quantity.
Close out our parentheses there,
close out our parentheses for the function,
and then we can hit Run.
So perfect.
So now we have our orders by our unique customers.
And then the last thing that we could do here is let's say
we wanted to add in an order by clause,
we could add in order by orders.
Scroll down here, you should be able to see that
a little bit better.
So orders, this is the calculated column we just created
and we wanna do this descending
so we have the highest values first.
Update that, and there you go.
So we just created this table
with a calculated column based on the order volume
within our orders fact table.
And needless to say, I'm sure
you're kind of wheels are spinning at this point,
there are a bunch of different options that you can employ
to create and explore tables.
And you know, sky is really the limit here
as long as you're returning a table at the end of the day.
But what if you wanted to evaluate a measure?
So let's dig into that
because the syntax is a little bit different.
So we will create a new query here.
And the first thing that we need to do
is start off with define.
So whenever we're creating a measure, we first need
to define that and we use the measure keyword,
and I want all of my measures
to live within my measures table.
I have this blank placeholder here,
but I wanna make sure that all of my measures
end up living within this table.
So I'm gonna create this
and we're gonna reference the measures table.
We're gonna call those Total Orders.
And this is going to = COUNTROWS(VALUES)
which is basically the distinct count
of our order ID, right?
And we'll close out our parentheses there.
So we've got our measure defined.
One of the cool things that ends up happening here
when you're creating new measures is you actually have
the option to add these into your data model.
So you see this little message appeared,
update data model add new measure.
If we were to click this, it's gonna add it into the table
that we've defined here in the first part
of our measure keyword.
So we're gonna wait until we see the results,
but like I had said earlier,
we have to have our evaluate keyword.
So let's add that in.
And then we're gonna use
our summarized columns option again.
Again, we'll create a little bit more space.
And let's say that I wanna look at all of my orders
by my different categories, right?
So instead of looking at this by customers,
let's actually look at these different orders by category.
So we're gonna reference the categories lookup
and the category name.
So this is the column that we first wanna group by.
And then from here, we're gonna look at total orders
and we can reference the total orders measure
that we just created and click Run.
So we've got this total orders measure by our distinct
or unique categories.
So that's super cool.
And say everything looks good,
we wanna add this into the data model,
we'll click add new measure,
confirm that we wanna do this.
And then in a moment here, we're gonna see it populate
in our measures table.
Awesome.
Do a little bit of hygiene, I'll delete
this placeholder column from the model.
And now we've got our measures table populated up here.
Now what's really cool here as well, is if you wanted
to add in an additional measure, all we need to do
is just add in another measure line item below this.
So we'll use our MEASURE keyword again,
and then we'll type a Measures Table.
And we're gonna define a new measure at this point
for Quantity Sold, right?
And this is going to = SUM('ORDERS (Fact)'[Quantity]).
And again, we'll make a little bit of space here.
And now that I have my measure defined up here,
all I need to do is add it into my summarized columns list.
So total, or we'll call this quantity sold perhaps.
Quantity Sold.
And again, I can reference the measure I just created.
We'll run the query.
And now I have a three column table
with my category name, total orders, and quantity sold.
Again, if everything looks good here,
we can update the model
and we're gonna add this into our measures table.
And let's say you had a different table that you wanted
to add these measures to.
Maybe you group things a little bit differently.
You can just change the table reference at the start
of the measure definition here,
and it's gonna add it into that table if you want.
All right, so that's kind of a quick exploration
of the new DAX query view.
And like you've seen, there's a ton of different stuff
that you can do within this view,
and I'm really excited to see
what else the Power BI team releases for this new feature.
But what do you think?
Do you think this will be helpful in your DAX
and report development?
Let me know what you think
and how you're planning on using the new DAX query view.