Get startedGet started for free

Foundational DAX measures and model calculations

1. Foundational DAX measures and model calculations

Now you'll use DAX to write and test measures that turn related tables into consistent business metrics.

2. Measures are reusable DAX formulas

Here's a measure that adds one column. Total Revenue, before the equals sign, is its name. SUM is the function, and Sales Amount, written as the table name with the column in square brackets, is what it adds. The measure stores this formula rather than a fixed result. Each report evaluates it in the current filter context, meaning the filters a visual applies, such as a selected month. Measures appear in the Data pane.

3. Reports evaluate measures in context

Filter context lets one measure serve many visuals. A card shows overall revenue, while a chart evaluates the same measure for each month or product. The formula stays the same, while only the filters change.

4. Source data and shared metric rules

Defining a measure once keeps reports consistent. If every report wrote its own revenue formula, totals could disagree. Which formula the measure needs depends on the source columns. If Sales stores Amount, the model can add it directly. If it stores only Quantity and UnitPrice, the formula must calculate the amount first.

5. Choosing a summing function

SUM takes one table-column reference. Sales Amount already holds the values, so SUM adds them. SUMX takes a table and a row expression. Here it visits each Sales row, multiplies Quantity by UnitPrice, then adds the results. Use SUM for one existing numeric column, and SUMX when each row needs a calculation first.

6. Two kinds of model calculations

Measures aren't the only model calculation. A measure returns a value for whatever a visual currently shows, so percentage of total sales changes by date, region, or product. A calculated column stores one value per row: an order-size category labels each order Large or Standard. If the result should change with report filters, use a measure; if it describes each row, use a calculated column. In Direct Lake models, calculated columns are limited (not supported on SQL endpoints and in preview on OneLake), so row labels like this are often added upstream in the lakehouse or warehouse.

7. Variables organize intermediate results

As measures grow, variables keep them readable. The first VAR line stores the sum of Amount as TotalSales; the second counts the Sales rows as SaleCount. After RETURN, DIVIDE divides one by the other and returns a blank instead of an error if there are no sales. Variables exist only while the measure is evaluated, so they don't create columns, and each step can be checked on its own.

8. Evaluating DAX expressions

Before saving a measure, you can test its DAX in DAX query view. Its tab sits at the bottom of the editor, next to Model view. Write the query in the top panel and select Run; the result appears in the panel below, and you can drag the border between them to resize. This query uses EVALUATE, which returns a table, and ROW, which builds a one-row table with a Total Sales column.

9. Copilot drafts require validation

Copilot can also help write DAX. On Copilot-enabled capacities, it can draft a query from a plain-language request in DAX query view, modify selected DAX, or explain a query. Treat what it returns as a first draft. A year-to-date calculation using DeliveryDate instead of OrderDate might run correctly but answer the wrong question. Compare the draft with your request, check its references, and test it against known values and filters.

10. Let's practice!

Let's practice!

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.