Introduction to DAX: Writing Your First Calculated Columns and Measures in Power BI

Introduction

Imagine you've imported a year's worth of sales data into Power BI. You have order dates, product names, quantities, and unit prices — but you don't have a total revenue column. You could go back to your source data, calculate it in Excel, re-import everything, and hope nothing changes. Or you could write three words in Power BI and have it calculated instantly, automatically, forever: [Quantity] * [Unit Price].

That three-word formula is DAX. Data Analysis Expressions (DAX) is the formula language built into Power BI (and also Excel Power Pivot and Analysis Services, if you ever work with those). It looks familiar if you've used Excel formulas, but it's fundamentally different under the hood — it's designed to work with tables and relationships, not individual cells. Once you understand that distinction, DAX clicks into place and becomes one of the most powerful tools in your analytical toolkit.

By the end of this lesson, you'll understand the difference between the two main building blocks of DAX — calculated columns and measures — and you'll know when to use each one. More importantly, you'll have actually written both, tested them, and seen them work in a real report context.

What you'll learn:

• What DAX is and how it differs from Excel formulas
• The difference between calculated columns and measures, and why that distinction matters
• How to write your first calculated column using basic DAX syntax
• How to write your first measures using aggregation functions like SUM, AVERAGE, and DIVIDE
• How to use the CALCULATE function to apply filters to your measures

---

Prerequisites

You should have Power BI Desktop installed (it's free — download it from microsoft.com/power-bi). You should know how to import a simple data table into Power BI and navigate to the Report and Data views. No prior DAX knowledge is required.

---

Understanding the DAX Mental Model

Before you write a single formula, you need to get one idea firmly in your head: DAX operates on tables, not cells.

In Excel, when you write =A2*B2, you're pointing at specific cells. You drag that formula down row by row, and each row gets its own copy. DAX doesn't work like that. When you write a DAX formula, you're writing a rule that applies to an entire column or defines a calculation that can evaluate across any slice of your data. You never drag DAX formulas anywhere.

Think of it this way: Excel formulas are like giving someone directions one step at a time. DAX formulas are like writing a policy — "whenever this calculation is needed, here's how to compute it." That shift in thinking is the most important thing to internalize before you start writing code.

---

Your Sample Dataset

Throughout this lesson, we'll work with a simple retail sales table. If you want to follow along, create a CSV with the following columns and paste in some rows of data:

OrderID, CustomerName, Product, Category, Quantity, UnitPrice, OrderDate, Region
1001, Sarah Chen, Wireless Keyboard, Electronics, 2, 49.99, 2024-01-05, North
1002, Marcus Rivera, Office Chair, Furniture, 1, 289.00, 2024-01-08, South
1003, Priya Nair, USB-C Hub, Electronics, 3, 34.99, 2024-01-12, North
1004, James Okafor, Standing Desk, Furniture, 1, 599.00, 2024-01-15, East
1005, Sarah Chen, Webcam, Electronics, 1, 89.99, 2024-01-19, North
1006, Leo Tanaka, Desk Lamp, Furniture, 2, 45.00, 2024-01-22, West
1007, Marcus Rivera, Mechanical Keyboard, Electronics, 1, 129.99, 2024-01-25, South
1008, Priya Nair, Monitor Stand, Furniture, 2, 79.99, 2024-01-28, North

Import this into Power BI Desktop by going to Home → Get Data → Text/CSV and selecting your file. Once it's loaded, you'll see your table in the Data view — click the table icon on the left sidebar to get there.

---

Calculated Columns: Adding Data Row by Row

A calculated column is a new column you add to an existing table using a DAX formula. Power BI evaluates that formula once for every row in the table and stores the result. The result is then part of your table permanently — you can see it in the Data view, use it in visuals, and filter on it just like any original column.

When to use a calculated column

Use a calculated column when:

• The result is specific to each row (like revenue per order)
• You need to use the result as a filter or slicer
• You want to categorize or label individual rows

Writing your first calculated column

Let's calculate the revenue for each order. Revenue is simply Quantity × UnitPrice.

In Power BI Desktop, go to the Data view by clicking the table icon on the left sidebar. Make sure your Sales table is selected. In the ribbon at the top, click Table Tools → New Column. A formula bar will appear at the top, waiting for your input.

Type this formula:

Revenue = [Quantity] * [UnitPrice]

Press Enter. Power BI will calculate the revenue for every single row immediately and add a new Revenue column to your table. You should see values like 99.98 for Sarah Chen's keyboard order (2 units × $49.99) and 289.00 for Marcus Rivera's chair.

A few things to notice about DAX syntax:

• Column names are always wrapped in square brackets: [Quantity]
• The name you type before the = sign becomes the column name: Revenue
• You never reference the table name in a calculated column formula (though you can — more on that shortly)

A slightly more useful calculated column: Profit Margin Tier

Let's say you want to categorize each order as "High Value" (over $200) or "Standard." This is where the IF function comes in.

Create another new column (Table Tools → New Column):

OrderTier = IF([Revenue] >= 200, "High Value", "Standard")

The IF function in DAX works exactly like in Excel: IF(condition, result_if_true, result_if_false). After you press Enter, you'll see each row labeled as either "High Value" or "Standard" based on its revenue. Now you can use OrderTier as a slicer in your report — that's the power of calculated columns.

Tip: Calculated columns increase the size of your data model because the results are stored in memory. For large datasets with millions of rows, think carefully before creating many calculated columns. Sometimes a measure is a better choice.

---

Measures: Calculations That Respond to Context

Here's where DAX gets genuinely interesting — and where it separates itself from anything Excel does natively.

A measure is a DAX formula that calculates a dynamic result based on whatever filters are currently applied in your report. A measure doesn't have a row-level value stored anywhere. Instead, it's recalculated on the fly every time your report renders, using exactly the data that's visible in that context.

Here's the analogy: a calculated column is like a column in a spreadsheet that you filled in at a specific point in time. A measure is like a formula that recalculates every time the view changes — but smarter, because it understands the full context of your report: which page you're on, which slicer values are selected, which category a chart bar represents.

When to use a measure

Use a measure when:

• You want to calculate totals, averages, counts, or ratios
• The result should change based on filters and slicers
• You're building KPIs or summary statistics for visuals

Writing your first measure

Let's calculate total revenue across all orders — or whatever subset of orders the user is currently looking at.

In Power BI Desktop, go to the Report view or stay in the Data view. In the ribbon, click Home → New Measure (or right-click your table name in the Fields pane and choose "New Measure"). The formula bar appears.

Type:

Total Revenue = SUM(Sales[Revenue])

Press Enter. Notice the syntax difference from the calculated column: Sales[Revenue] includes the table name. In measures, it's a best practice — and often a requirement — to use the fully qualified TableName[ColumnName] format, because a measure doesn't belong to any specific table the way a calculated column does.

SUM is an aggregation function — it collapses many rows into a single value. In this case, it adds up every value in the Revenue column.

Now, drag Total Revenue into a Card visual on your report canvas (Insert → New Visual → Card, then drag your measure to the Values field). You'll see a single number: the total revenue across all orders. Now add a Region slicer (drag the Region column onto the canvas and set the visual type to Slicer). Click "North" — your Total Revenue card instantly updates to show only North region revenue. The measure responded to context automatically. You didn't change the formula at all.

That is what makes measures so powerful.

More useful measures: AVERAGE and COUNT

Let's add a few more measures to build out our report.

Average order value:

Avg Order Value = AVERAGE(Sales[Revenue])

Number of orders:

Order Count = COUNTROWS(Sales)

COUNTROWS counts the number of rows in a table — in this case, the number of orders visible in the current filter context. These two measures, combined with your Region slicer, give you instant breakdowns by region without any extra work.

Warning: Don't confuse COUNT with COUNTROWS. COUNT(Sales[Revenue]) counts rows where Revenue is not blank, while COUNTROWS(Sales) counts all rows in the table. For most scenarios, COUNTROWS is what you want when you're counting records.

---

The DIVIDE Function: Handling Division Safely

You'll often need to calculate ratios in business reports — things like profit margin, conversion rate, or revenue per customer. Division in DAX has one nasty habit: if you divide by zero, Power BI shows an error. The DIVIDE function handles this gracefully.

Let's calculate revenue per order:

Revenue Per Order = DIVIDE(SUM(Sales[Revenue]), COUNTROWS(Sales))

DIVIDE takes three arguments: DIVIDE(numerator, denominator, alternate_result). The third argument is optional — if you leave it out, Power BI returns BLANK instead of an error when the denominator is zero. That's almost always preferable to an error message appearing in your visuals.

A safer version with an explicit fallback:

Revenue Per Order = DIVIDE(SUM(Sales[Revenue]), COUNTROWS(Sales), 0)

This returns 0 if there are no orders in the current context. Whether you prefer BLANK or 0 depends on your use case — BLANK is often cleaner in visuals because charts simply won't draw a data point for it.

---

CALCULATE: The Most Important DAX Function

If you learn only one advanced DAX concept from this lesson, make it CALCULATE. Understanding CALCULATE is the bridge between beginner and intermediate DAX.

CALCULATE does two things: it evaluates an expression, and it lets you modify the filter context for that expression. That second part is what makes it invaluable.

Here's the signature:

CALCULATE(expression, filter1, filter2, ...)

A practical example

Suppose you want to always show Electronics revenue on your dashboard, regardless of what the user has filtered. You want a reference point — a benchmark to compare against other categories.

Electronics Revenue = CALCULATE(SUM(Sales[Revenue]), Sales[Category] = "Electronics")

No matter what slicer the user clicks, Electronics Revenue will always show the total revenue for Electronics. The CALCULATE function overrides the filter on the Category column with its own filter.

Comparing current selection to total

One of the most common use cases for CALCULATE is creating a "total regardless of filter" measure, which you then use in a ratio. For example, to show what percentage of total revenue came from the selected region:

First, create a measure for total revenue ignoring the region filter:

Total Revenue All Regions = CALCULATE(SUM(Sales[Revenue]), ALL(Sales[Region]))

ALL(Sales[Region]) is a DAX function that removes all filters on the Region column. Now you can use it in a ratio:

Revenue % of Total = DIVIDE(SUM(Sales[Revenue]), [Total Revenue All Regions])

Format this measure as a percentage (select the measure in the Fields pane → Measure Tools → Format → Percentage). Now drop it into a table visual alongside Region and Total Revenue, and you'll see each region's contribution as a percentage of the grand total — even when the Region slicer is active.

Tip: CALCULATE is how you solve the vast majority of "I want this calculation to ignore a specific filter" problems in Power BI. When you find yourself stuck on a DAX problem, there's a good chance CALCULATE is part of the answer.

---

Calculated Columns vs. Measures: The Decision Framework

By now you've used both. Here's a concise way to decide which to reach for:

Scenario	Use
Calculating row-level values (price × quantity)	Calculated Column
Creating categories or labels for each row	Calculated Column
Filtering or slicing by a computed value	Calculated Column
Calculating totals, averages, ratios for visuals	Measure
Building KPIs that respond to user selections	Measure
Comparing segments or performing what-if analysis	Measure

The deeper principle: if the result exists at the row level and doesn't change based on report context, it's a column. If the result needs to respond to filters, it's a measure.

---

Hands-On Exercise

Complete the following tasks using the Sales dataset from this lesson:

1. Calculated Column: Discount Amount Add a column called Discounted Revenue that applies a 10% discount to any Electronics order and no discount to other categories. Use a nested IF or consider the SWITCH function if you want a challenge.

Hint:

Discounted Revenue = IF([Category] = "Electronics", [Revenue] * 0.9, [Revenue])

2. Measure: High Value Order Count Write a measure that counts only orders with Revenue greater than or equal to $200. Use CALCULATE and COUNTROWS together.

Hint: You'll need CALCULATE(COUNTROWS(Sales), Sales[Revenue] >= 200).

3. Measure: Revenue by Category Comparison Create a table visual on your report canvas with Category in the rows, and drop in both Total Revenue and your Electronics Revenue measure. Observe what happens to Electronics Revenue when you add a Region slicer and make a selection. Can you explain why it behaves the way it does?

4. Build a mini dashboard Add a Card visual for Total Revenue, another for Order Count, a slicer for Region, and a bar chart showing Total Revenue broken down by Category. Observe how all visuals respond when you click a region in the slicer.

---

Common Mistakes & Troubleshooting

"My measure shows the same number everywhere in my table" You're likely dragging a calculated column into a visual where a measure is expected, or you've wrapped your aggregation incorrectly. Check that your measure uses SUM, AVERAGE, COUNTROWS, or another aggregation — a measure that returns [Revenue] without an aggregation function will cause problems.

"DAX syntax error: The column '[Revenue]' could not be found" In a measure, always use the fully qualified TableName[ColumnName] syntax. Sales[Revenue] instead of just [Revenue]. Unqualified column references work in calculated columns but can fail in measures.

"My CALCULATE filter isn't working as expected" Remember that CALCULATE overrides existing filters on the columns you specify. If you expected it to add to the current filter, you may need to use KEEPFILTERS — but that's an intermediate topic. For now, just be aware that CALCULATE replaces, not combines, filters on the specified column.

"My calculated column shows BLANK for some rows" Check whether the columns you're referencing have blank or null values in some rows. DAX propagates blanks — any arithmetic involving a blank returns blank. Use IF(ISBLANK([UnitPrice]), 0, [UnitPrice]) to substitute a default value.

"I can't find my measure in the Fields pane" Measures appear in the table you created them in, but they're shown with a calculator icon (not a table column icon). If you created a measure on the wrong table, you can move it: right-click the measure in the Fields pane → Home Table → select the correct table.

---

Summary & Next Steps

You've covered a lot of ground. Here's what you now know:

• DAX is a formula language that operates on tables and relationships, not individual cells
• Calculated columns are evaluated row by row and stored in the table — use them when you need row-level data or filterable categories
• Measures are dynamic calculations that respond to report context — use them for aggregations, KPIs, and anything driven by user filters
• Core functions like SUM, AVERAGE, COUNTROWS, IF, and DIVIDE let you handle the most common business calculations
• CALCULATE is the gateway to advanced DAX — it lets you evaluate expressions in a modified filter context, unlocking comparisons and benchmarks

The gap between "I know what DAX is" and "I can build real reports with DAX" comes down to practice. The best way to build fluency is to take a dataset you already care about and start asking questions of it — then figure out which DAX formula answers each question.

Where to go next:

• Time Intelligence in DAX: Learn functions like DATEYTD, SAMEPERIODLASTYEAR, and TOTALYTD to build period-over-period comparisons
• Understanding Filter Context and Row Context: A deeper dive into how DAX evaluates formulas will unlock more advanced patterns
• Iterator Functions (SUMX, AVERAGEX): These let you calculate row by row and then aggregate — essential when your aggregation logic is more complex than a simple column sum
• Variables in DAX: Use VAR and RETURN to write cleaner, more readable formulas and improve performance

Every powerful Power BI report you've ever admired is built on exactly the foundation you laid today.