All language subtitles for 7. Implicit vs. Explicit DAX Measures

So I hinted at this a little bit earlier but I want to take a minute to unpack this idea of implicit

versus explicit measures now in power be i in the report view implicit measures are created when you

drag raw numerical fields fields like order quantity from our sales data table into the values pain

of a visual now what power be does is automatically select and apply an aggregation mode usually a sum

so that you can view those numbers those values in a meaningful way.

And that's exactly what's been going on with all of the values that we've been looking at in the matrix

views that we've built now on the other hand explicit measures are created by actually entering tax

functions or which we're not going to do quick measures to explicitly define calculated columns or measures.

So very important difference here implicit measures are only accessible within the specific visualization

in which they're created.

You can't reference that sum of Order Quantity anywhere else.

Same exact idea as a calculated field in an Excel pivot table.

It can only be accessed and analyzed in that place.

But what's great about explicit measures is that they can be used anywhere in the report.

Once you create them and referenced within other tax calculations to create more complex formulas or

something that we call measure trees.

So for instance if we wanted to take this sum of order quantity and then ness that within another function

like total orders minus total returns to come up with net orders or something similar we would have

to create an explicit measure in order to access those component values.

So let's jump in a power by and I'll show you what I mean.

All right.

So we're back in the report pane of our power by adventure Works report just like we left it and here

we're looking at order quantities and return quantities by product keys.

And now again if you look at the actual details you'll see that it's defaulted to an aggregation mode

of some in both of these cases and that's kind of the standard behavior when you drag in a numerical

field like this.

So let's get rid of return quantity but keep this version of order quantity in here.

We're just going to drag this closer.

And now the idea is that I want to create a proper explicit measure to capture that same idea.

That same sum of order quantity but I then want to be able to use and access that sum in other measures

and calculations down the line.

So I'm going to show you a couple approaches like we talked about.

Number one I could go into modeling and click new measure and you see what happened when I did that.

It created a new line item a new field for this measure and it placed it in the AWB calendar lookup

table since that's the first one in our field list.

And I'm trying to create a measure related to the sum of order quantity so it doesn't make any sense

whatsoever for that measure to live in the calendar look up which is why I don't recommend using that

approach using the modeling tab to create your measures.

So instead of even typing it in here I'm going to go ahead and x out which just cancels that and instead

I'm going to right click on the sales table or anywhere within the sales table I'm going to choose new

measure from that menu.

Now it's added the measure assigned it to the sales table where it makes a lot more sense to live.

So to define this I'm going to give it a name let's call it quantity sold.

And it's okay to use spaces in measures.

They're always gonna be reflected in formulas surrounded by brackets.

So in this case spaces don't really have any negative impact.

And in fact they can often make them a bit more readable.

So when do quantity sold with a space equals the sum of the Order Quantity column.

And again this intel sense this autocomplete functionality is really user friendly so I'm just going

to literally start typing order.

I see that there are four columns within the W sales table that begin with order and we want order quantity

so I can either arrow down and TAB or I can click and then double click again to lock it in.

So it's really as simple as that the sum function has one argument it's the column name so I can close

the parentheses and press enter and now looking over it I feel list.

It's created a new field here called quantity sold.

Now let's grab that pull it in right under our order quantity and you'll see that it returns the exact

same numbers which is good.

That's exactly what we want here.

But again the first version the implicit version of this sum can not be accessed outside of this visual

whereas the measure version of the sum which we've named quantity sold can.

And that's what makes it way more powerful and way more flexible.

So one of the best practices that we're going to talk more about is to create explicit measures even

for very simple things that you could define with the auto aggregation available implicitly getting

in the habit of using explicit measures will only make things easier for you down the road.

So there you go.

That's a simple example of creating our first true explicit measure.

If we did want to move or reassign that measure this is where you do it.

This little Properties Group in the modelling tab.

You can just click the home table and assign it somewhere else.

In this case it makes sense to live in the sales table.

Since we're aggregating a sale specific field and you'll probably notice that you're seeing different

kinds of icons next to the field in the field list the one that we just created the measure as a little

calculator icon next to it.

That's always going to be your indication of a calculated measure whereas the Order Quantity has that

Sigma symbol next to it because it's a raw numerical field and you also have a different icon with the

little effects.

Looks like a small table that indicates a calculated column.

So we have all three types here within our AWP sales data.

So there you go.

That's your first explicit measure.

Congratulations.