~12 min read

Understanding DAX

The Language Behind Power BI

From familiar Excel formulas to powerful data models. Part 1 of the DAX Fundamentals series.

Part 1 of 3: DAX Fundamentals
Scroll to explore

What is DAX?

The formula language that powers everything in Power BI

If you've used Excel formulas, you already know more DAX than you think. DAX (Data Analysis Expressions) is the formula language behind Power BI, Analysis Services, and Power Pivot. It's a function language, so there are no loops or cell references. You compose functions together, and the engine figures out which rows to evaluate based on your data model.

Microsoft brought over 80+ functions straight from Excel (SUM, IF, AVERAGE, the ones you already know), then added 100+ DAX-only functions like CALCULATE, FILTER, and ALL. Over 250 functions total. You do not need to memorize them all (honestly, nobody has).

DAX gives you three types of calculations. The difference between them shapes everything from performance to whether your numbers come out right:

Measures

Dynamic calculations evaluated at runtime. They respond to filters, slicers, and context in your visuals. The most common calculation type in DAX.

Recommended Default

Calculated Columns

New columns added to tables, calculated row by row during refresh. Stored in memory. Use for values you'll filter or sort by.

Stored Per Row

Calculated Tables

Entirely new tables generated from DAX expressions. Perfect for date tables, bridge tables, or summarized snapshots.

Materialized Table

Why should you care? Pick the wrong calculation type and you get bloated models, sluggish visuals, or numbers that just look wrong. We'll dig into each type in the next section.

From Excel to DAX

The mental model shift that changes everything

In Excel, you point at cells. A1, B2:B10, you know the drill. DAX doesn't work that way. DAX formulas operate on columns and tables, and which rows actually get included depends entirely on context (filters, slicers, relationships). The formula stays the same; the context changes the result. This single concept is the biggest mental shift you'll make.

A lot of the functions you already know exist in both languages. Here's how they compare side by side:

Excel

=SUM(B2:B100)

Sums a specific range of cells.

vs

DAX

Total Revenue = SUM( 'Trades'[TradeTotal] )

Sums the entire column. Context determines which rows contribute.

Key Insight: No cell references. DAX sums the column, filtered by context. Select "2225" in a slicer and only 2225 rows are summed. The formula doesn't change; the context does.

Excel

=IF(A2="High",3,
  IF(A2="Medium",2,
    IF(A2="Low",1,0)))

Nested IFs get messy fast.

vs

DAX

Priority Score =
SWITCH(
  TRUE(),
  [Priority] = "High", 3,
  [Priority] = "Medium", 2,
  [Priority] = "Low", 1,
  0
)

SWITCH(TRUE(), ...) is clean and scales to any number of conditions.

Key Insight: SWITCH replaces nested IF chains. The SWITCH(TRUE(), ...) pattern evaluates conditions in order and returns the first match. So much cleaner.

Excel

=VLOOKUP(A2, Cargo, 3, FALSE)

Manual lookup by column index.

vs

DAX

-- Relationships handle this!
-- Just use: 'Cargo'[Class]

The data model's relationships replace manual lookups entirely.

If your tables are properly related, you can reference columns directly. RELATED() exists for edge cases, but the model usually handles it for you.

Excel

=AVERAGE(B2:B100)

Averages a fixed range. One level of aggregation.

vs

DAX

Avg Daily Revenue =
AVERAGEX(
  VALUES( 'Stardate'[Date] ),
  [Total Revenue]
)

Averages Total Revenue per unique date. You control the grain.

Key Insight: Iterator functions like AVERAGEX evaluate row-by-row over a table, then aggregate the results. Excel simply cannot do this in a single formula.

Excel

=COUNTA(A2:A100)
=COUNTIF(B2:B100, "Minerals")

COUNT for numbers, COUNTA for non-blanks, COUNTIF for conditions.

vs

DAX

Trade Count = COUNTROWS( 'Trades' )

Mineral Trades =
CALCULATE(
  COUNTROWS( 'Trades' ),
  'Cargo'[Class] = "Minerals"
)

COUNTROWS counts table rows; CALCULATE adds filter conditions.

Pro tip: Prefer COUNTROWS over COUNT in DAX. COUNT only works on columns; COUNTROWS counts table rows in the current filter context, which is much more flexible.

Excel

=A2 & " - " & B2
=TEXTJOIN(", ", TRUE, A2:A10)

Ampersand for joining cells; TEXTJOIN for ranges.

vs

DAX

Cargo Label =
'Cargo'[Class] & " - " & 'Cargo'[Name]

Class List =
CONCATENATEX(
  VALUES( 'Cargo'[Class] ),
  'Cargo'[Class], ", "
)

Ampersand works the same; CONCATENATEX iterates and joins.

Pro tip: CONCATENATEX is DAX's TEXTJOIN equivalent. It iterates over a table and joins text with a delimiter. Super useful for creating dynamic labels in measures.

Excel

=YEAR(A2)
=EOMONTH(A2, 0)
=DATEDIF(A2, TODAY(), "M")

Individual date functions applied per cell.

vs

DAX

Revenue YTD =
TOTALYTD( [Total Revenue], 'Stardate'[Date] )

Prior Cycle =
CALCULATE(
  [Total Revenue],
  DATEADD( 'Stardate'[Date], -1, YEAR )
)

Built-in time intelligence: YTD, prior period, rolling windows.

Key Insight: DAX has an entire family of time intelligence functions that make year-over-year, quarter-to-date, and rolling-average calculations trivial. Excel has nothing even close.

Calculation Types

Measures, calculated columns, and calculated tables, and when to use each

You have three core tools in DAX: measures, calculated columns, and calculated tables. Each one is built for different scenarios, and picking the right one directly impacts performance, memory usage, and whether your report actually behaves the way you expect. Get it wrong and you end up with a model that's slow, bloated, or just confusing. So let's break each one down.

Measures

The workhorse of DAX. Evaluated on the fly every time a visual renders. The result depends entirely on the filter context.

Profit Margin = DIVIDE( [Net Profit], [Total Revenue] )
Recommended Default

Calculated Columns

New columns evaluated row by row during data refresh. Stored in memory. Use when you need values to filter, sort, or slice by.

Value Tier = IF( 'Cargo'[Value] > 100, "Premium", "Standard" )
Uses Memory Per Row

Calculated Tables

Entirely new tables from DAX expressions. Materialized during refresh. Use for date tables, bridge tables, or distinct value lists.

Stardate = CALENDARAUTO()
Structural / Niche

Key Insight: When in doubt, use a measure. Seriously. Measures do not consume per-row memory, they respond dynamically to whatever the user selects, and they're the right choice roughly 99% of the time. Only reach for calculated columns or calculated tables when you have a specific structural reason to.

Still not sure? Walk through this:

Which Calculation Type Should You Use?

Do you need a value for each row in a table?

Will you use this value to filter, sort, or slice a visual?

Will this aggregate be used in report visuals?

Is this a helper table, bridge table, or date table?

Measure

Use a Measure

Measures are what you'll write most often in DAX. They evaluate at runtime based on filter context, making them perfect for KPIs, totals, ratios, and any value that changes based on what the user is looking at.

Rule of thumb: About 99% of the time, you want a Measure. Calculated columns are tempting because you can see the data row by row, but measures are almost always the better choice.

Calculated Column

Use a Calculated Column

When you need a row-level value that users can filter or sort by (like a category label, age bucket, or profit tier), a calculated column is the right choice. Just remember: it is stored in memory for every row.

Consider a Measure

A Measure Might Still Work

If you just need to display a calculated value (not filter by it), a measure is usually more efficient. Measures don't consume memory per row and respond dynamically to context.

Calculated Table

Use a Calculated Table

Calculated tables are perfect for bridge tables, date tables, or summarized reference data. They're materialized (stored in memory), so use them sparingly for specific structural needs.

Syntax and References

How to read and write DAX: the building blocks of every formula

Reference Types

DAX syntax has three types of references. Bookmark this one:

'TableName' → Single quotes for tables (required if name has spaces)
'Table'[Column] → Brackets for columns. Always include the table prefix
[MeasureName] → Brackets only for measures. No table prefix needed
Operators
+ - * / → Arithmetic operators
= <> < > <= >= → Comparison operators
&& || → Logical AND, logical OR
& → Text concatenation
IN { } → Multiple OR conditions (e.g., [Origin] IN {"Andara", "Cobalt"})

You do not need to memorize all 250+ functions. But knowing the categories exist means you'll know where to look when you need something:

Aggregation Functions

SUM AVERAGE COUNT DISTINCTCOUNT MIN MAX
Total Revenue = SUM( 'Trades'[TradeTotal] )

Filter Functions

CALCULATE FILTER ALL ALLEXCEPT VALUES SELECTEDVALUE
Revenue All Cargo =
CALCULATE( [Total Revenue], ALL( 'Cargo' ) )

Logic Functions

IF SWITCH AND OR TRUE FALSE
Status =
IF( [Profit Margin] > 0.2, "Healthy", "At Risk" )

Iterator Functions

SUMX AVERAGEX COUNTX MINX MAXX RANKX
Total Credits =
SUMX( 'Trades', 'Trades'[Units] * 'Trades'[UnitCost] )

Time Intelligence

DATESYTD DATESQTD DATEADD DATESBETWEEN TOTALYTD
Revenue YTD =
TOTALYTD( [Total Revenue], 'Stardate'[Date] )

Text and Info

CONCATENATE FORMAT LEFT RIGHT UPPER LOWER
Revenue Display =
FORMAT( [Total Revenue], "$#,##0" )

The Data Model

Why the structure of your data matters more than any formula you'll write

Here's something that trips people up early: DAX is only as good as the data model underneath it. You can write perfect formulas and still get wrong answers if the model is messy. The recommended structure is called a star schema, and it's built from two types of tables:

Fact Tables

The center of your model. Transactional data: orders, sales, events. Each row is something that happened. Contains foreign keys and numeric columns that measures aggregate.

Events & Transactions

Dimension Tables

The "who, what, when, where" context. Cargo, Merchants, Stardate. Each has a unique key and descriptive attributes users filter and slice by.

Descriptive Context

Picture it like a hub and spokes. The fact table sits in the center, with dimension tables radiating outward. They're connected by one-to-many relationships, meaning each dimension row (1) can match many fact rows (*):

Dimension

Cargo

  • CargoKey (PK)
  • CargoName
  • Class
  • Value
Dimension

Stardate

  • StardateKey (PK)
  • Cycle
  • Quarter
  • Month
Dimension

Merchants

  • MerchantKey (PK)
  • MerchantName
  • Sector
  • Segment
1 1 1 * * *
Fact Table

Trades

  • TradeKey (PK)
  • CargoKey (FK)
  • StardateKey (FK)
  • MerchantKey (FK)
  • Units
  • TradeTotal

Why This Matters for DAX: Star schema enables filter propagation. When someone selects "Minerals" in a slicer, the filter flows from the Cargo dimension, down the relationship, into the Trades fact table. Only Mineral rows survive. This automatic flow is what makes your measures work without any extra code.

Filter Flow

See how filters propagate through the star schema in real time

When someone picks "Minerals" in a slicer, the filter hits Cargo first, then flows down the relationship to Trades. Only matching rows survive. Every measure evaluates against that filtered subset. Try it yourself; click around and watch the numbers change:

Cargo Class

Cycle

Cargo (Dimension)
Stardate (Dimension)
Trades (Fact)
Rows in Context
20 / 20
filtered rows
Total Units
4,500
SUM( 'Trades'[Units] )
Total Revenue
$4,972,000
SUM( 'Trades'[Credits] )

This is honestly the thing that makes DAX click for most people. The measure Total Revenue = SUM('Trades'[Credits]) never changes. The formula is identical every time. But the result changes because the filter context changed. Once you internalize this, everything else in DAX starts to make sense. That's exactly what Part 2 is about: How DAX Thinks: Context is Everything.

Key Takeaways

What you've learned in Part 1

250+ Functions, Zero Cell Refs

DAX is a function language. 80+ functions come from Excel; 100+ are unique to DAX.

Context Drives Results

DAX operates on columns and tables, not cell references. Context determines which rows contribute to the result.

Measures Are Your Default

Measures are evaluated at runtime and should be your default choice roughly 99% of the time.

Columns Store, Tables Structure

Calculated columns store row-level values; calculated tables create new structures in the model.

Star Schema Is the Foundation

Fact tables surrounded by dimension tables is the architecture that makes DAX work effectively.

Filters Flow Automatically

Filters propagate from dimensions to fact tables through relationships without any extra code.

Next up: filter context and row context, how CALCULATE works, and why understanding context is the key to writing DAX that behaves the way you expect.

Continue to Part 2

How DAX Thinks: Context is Everything

Filter context, row context, CALCULATE, and context transition. The concepts that separate beginners from experts.

Part 2 of 3
1 / 9