Quick measures – Creating Measures Using DAX

Quick measures

Quick measures are a capability that allows you to use a drag-and-drop interface to quickly create measures with a minimal amount of code. Quick measures are a great way to not only easily create measures but also learn DAX. You can look at the code produced by quick measures and see some of the more esoteric functions, such as how to calculate a weighted average of the base value for each category.

See Chapter 5, Designing a Data Model, for more information about quick measures.

Creating your own measure

So, how do we create these DAX calculations? We already touched upon quick measures, but you may have noticed that you can right-click on a table in the Fields list and choose the New measure option. Alternatively, you can select the Modeling tab from Report view or the Table tools tab from Data view to create a new measure.

Figure 7.2 – How to create a new measure. 1 is the context menu. 2 is the Modeling tab from Report view. 3 is Table tools from Data view

Once you have selected one of these options, the formula bar will appear and you can start typing your new measure.

Measures versus calculated columns

DAX provides a way to add or generate data using data brought in from other data sources. This provides a way to build a more complete picture using the data you have. Both measures and calculated columns can do this but there are nuances and pros and cons to each, which we’ll look at later in this chapter.

Suppose you want to create a new column in a sales table for gross revenue. As some of you may know, gross revenue is usually calculated as quantity * unit price. What we can’t do is sum up the number of things sold for the day and multiply it by the sum of the unit prices of the things sold for the day. That would not lead to an accurate number. If I sold one widget for $3 and two gadgets for $6, I did not have $18 worth of sales; I had only $15. I must go line by line with this calculation:

Gross Revenue = Sales[Quantity] * Sales[UnitPrice]

This is the classic use for a calculated column.

Later in this chapter, in the Replacing numeric calculated columns with measures section, we will explain why the best answer for this may not be calculated columns and how to craft a measure that goes line by line.

Leave a Reply

Your email address will not be published. Required fields are marked *

Shopping cart

close