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.