All language subtitles for 004 DAX Query View (November 2023)_en

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.