~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

DAX (Data Analysis Expressions) is a formula language built for tabular data models -- the kind you work with in Power BI, Analysis Services, and Power Pivot. It's a function language: no loops, no cell references. You compose functions together, and the engine evaluates them against your data model.

Microsoft migrated 80+ functions directly from Excel (SUM, IF, AVERAGE, etc.), then added 100+ DAX-only functions like CALCULATE, FILTER, and ALL. There are now over 250 DAX functions.

DAX creates three types of calculations -- understanding the difference is one of the most important things you'll learn:

Measures

Dynamic calculations evaluated at runtime. They respond to filters, slicers, and context in your visuals. The backbone of 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 does this matter? Choosing the right calculation type directly impacts performance, memory usage, and report behavior. We'll dig deeper into each type in the next sections.

From Excel to DAX

The mental model shift that changes everything

In Excel, formulas operate on explicit cell references -- you point at A1, reference B2:B10. DAX works differently: formulas operate on columns and tables, and which rows get evaluated depends on context (filters, slicers, relationships). The formula stays the same; the context changes the result.

Many functions you already know exist in both languages. Compare them side by side:

Excel

=SUM(B2:B100)

Sums a specific range of cells.

vs

DAX

Total Sales = SUM( 'Sales'[LineTotal] )

Sums the entire column -- context determines which rows contribute.

Key Insight: No cell references. DAX sums the column, filtered by context. Select "2023" in a slicer and only 2023 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.

Excel

=VLOOKUP(A2, Products, 3, FALSE)

Manual lookup by column index.

vs

DAX

-- Relationships handle this!
-- Just use: 'Products'[Category]

The data model's relationships replace manual lookups entirely.

Key Insight: Properly related tables let you reference columns directly. RELATED() exists for edge cases, but the model usually handles it.

Excel

=AVERAGE(B2:B100)

Averages a fixed range. One level of aggregation.

vs

DAX

Avg Daily Sales =
AVERAGEX(
  VALUES( 'Calendar'[Date] ),
  [Total Sales]
)

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

Key Insight: Iterator functions like AVERAGEX evaluate row-by-row over a table, then aggregate. Excel can't do this in a single formula.

Excel

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

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

vs

DAX

Order Count = COUNTROWS( 'Sales' )

Bike Orders =
CALCULATE(
  COUNTROWS( 'Sales' ),
  'Products'[Category] = "Bikes"
)

COUNTROWS counts table rows; CALCULATE adds filter conditions.

Key Insight: Prefer COUNTROWS over COUNT in DAX. COUNT only works on columns; COUNTROWS counts table rows in the current filter context -- much more flexible.

Excel

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

Ampersand for joining cells; TEXTJOIN for ranges.

vs

DAX

Product Label =
'Products'[Category] & " - " & 'Products'[Name]

Category List =
CONCATENATEX(
  VALUES( 'Products'[Category] ),
  'Products'[Category], ", "
)

Ampersand works the same; CONCATENATEX iterates and joins.

Key Insight: CONCATENATEX is DAX's TEXTJOIN equivalent. It iterates over a table and joins text with a delimiter -- great for creating dynamic labels in measures.

Excel

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

Individual date functions applied per cell.

vs

DAX

YTD Sales =
TOTALYTD( [Total Sales], 'Calendar'[Date] )

Prior Year =
CALCULATE(
  [Total Sales],
  DATEADD( 'Calendar'[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 comparable.

Calculation Types

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

DAX gives you three core calculation types -- measures, calculated columns, and calculated tables -- each designed for different scenarios. Choosing the right one directly impacts your model's performance, memory usage, and behavior in reports. Get it wrong, and you end up with bloated models, sluggish visuals, or results that don't match expectations. Here's how each type works and when to reach for it.

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.

Price Tier =
SWITCH( TRUE(),
  'Products'[Price] > 1000, "Premium",
  'Products'[Price] > 100, "Standard",
  "Budget"
)
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.

Calendar =
CALENDARAUTO()
Structural / Niche

Rule of thumb: When in doubt, use a measure. Measures don't consume per-row memory, they respond dynamically to user selections, and they're almost always the right choice. Only reach for calculated columns or calculated tables when you have a specific structural reason.

Not sure which type to use? Walk through the decision flowchart:

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 the backbone of 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's 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

DAX syntax has three types of references. Here's the quick-reference cheat sheet:

Reference Types
'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., [Color] IN {"Red", "Blue"})

You don't need to memorize all 250+ functions -- but knowing which categories exist helps you know where to look:

Aggregation Functions

SUM AVERAGE COUNT DISTINCTCOUNT MIN MAX
Total Sales = SUM( 'Sales'[LineTotal] )

Filter Functions

CALCULATE FILTER ALL ALLEXCEPT VALUES SELECTEDVALUE
Sales All Products =
CALCULATE( [Total Sales], ALL( 'Products' ) )

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
Revenue =
SUMX( 'Sales', 'Sales'[Qty] * 'Sales'[UnitPrice] )

Time Intelligence

DATESYTD DATESQTD DATEADD DATESBETWEEN TOTALYTD
YTD Sales =
TOTALYTD( [Total Sales], 'Calendar'[Date] )

Text and Info

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

The Data Model

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

DAX is only as good as the data model underneath it. The recommended structure is called a star schema, 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. Products, Customers, Calendar. Each has a unique key and descriptive attributes users filter and slice by.

Descriptive Context

The fact table sits in the middle with dimensions radiating outward, connected by one-to-many relationships -- each dimension row (1) can appear in many fact rows (*):

Dimension

Products

  • ProductKey (PK)
  • ProductName
  • Category
  • Price
Dimension

Calendar

  • DateKey (PK)
  • Year
  • Quarter
  • Month
Dimension

Customers

  • CustomerKey (PK)
  • CustomerName
  • Region
  • Segment
1 1 1 * * *
Fact Table

Sales

  • SalesKey (PK)
  • ProductKey (FK)
  • DateKey (FK)
  • CustomerKey (FK)
  • Quantity
  • LineTotal

Why this matters for DAX: Star schema enables filter propagation. When a user selects "Bikes," the filter flows from the Products dimension down the relationship to the Sales fact table. Only Bike rows survive. This automatic flow is what makes measures work.

Filter Flow

See how filters propagate through the star schema in real time

When a user picks "Bikes" in a slicer, the filter hits the Products dimension first, then flows down the relationship to the Sales fact table. Only matching rows survive. Every measure evaluates against that filtered subset. Try it yourself:

Product Category

Year

Products (Dimension)
Calendar (Dimension)
Sales (Fact)
Rows in Context
20 / 20
filtered rows
Total Quantity
4,500
SUM( 'Sales'[Qty] )
Total Sales
$4,972,000
SUM( 'Sales'[Amount] )

This is the magic of DAX. The measure Total Sales = SUM('Sales'[Amount]) never changes. The formula stays the same -- the result changes because the filter context changes. This is exactly what we'll explore in depth in Part 2: How DAX Thinks: Context is Everything.

Key Takeaways

What you've learned in Part 1

  • DAX is a function language with 250+ functions -- 80+ inherited from Excel, 100+ unique to DAX
  • DAX operates on columns and tables, not cell references -- context determines results
  • Measures are evaluated at runtime and should be your default choice (~99% of the time)
  • Calculated columns store row-level values; calculated tables create new structures
  • Star schema (fact + dimension tables) is the foundation that makes DAX work
  • Filters propagate from dimensions to fact tables through relationships automatically

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 / 8