DAX (Data Analysis Expressions) is the formula language behind Power BI's brain. It allows you to create custom calculations, aggregations, and filters beyond the built-in visual options — making your reports smarter and more interactive.

Quick Answer

DAX (Data Analysis Expressions) is Power BI's formula language for creating measures and calculated columns. The most important function is CALCULATE — it evaluates any expression in a modified filter context, enabling percentage of totals, year-over-year comparisons, and dynamic segmentation.

What it isA functional language used in Power BI, Excel Power Pivot, and SSAS to define business calculations
Measures vs ColumnsMeasures calculate dynamically based on filter context; calculated columns store per-row values in memory
Common mistakeUsing a calculated column when a measure is needed — columns bloat model size and don't respond to slicers
Certification relevancePL-300 Domain: Model Data (25-30% of exam). CALCULATE with ALL/REMOVEFILTERS is a high-frequency question

💬 Real-World Analogy — Excel on Steroids

If you've used Excel formulas like SUMIF() or VLOOKUP(), you already understand the idea behind DAX. But DAX goes significantly further:

📊 Excel Formulas
  • Work on a single sheet or range
  • Calculated at the cell level
  • Static — don't respond to slicers
  • No cross-table awareness
⚡ DAX in Power BI
  • Work across multiple related tables
  • Recalculate dynamically per visual context
  • Respond instantly to slicers and filters
  • Understand model relationships automatically

🧮 The Three Types of DAX Expressions

DAX can produce three fundamentally different outputs. Choosing the right one is the first skill to master:

📌 Calculated Column
  • Stored permanently in the data model
  • Calculated row-by-row at refresh time (Row Context)
  • Increases model size — use sparingly
  • Good for: categorisation, bucketing, combining fields
Example
Profit = Sales[Total] - Sales[Cost]
📊 Measure ✅ Preferred
  • Calculated on the fly when a visual renders
  • Responds dynamically to filters, slicers, and visual context (Filter Context)
  • Memory-efficient — not stored in the model
  • Good for: aggregations, KPIs, % of total, YTD
Example
Total Sales = SUM(Sales[Amount])
🧪 Calculated Table
  • Returns a full table instead of a value or column
  • Stored in the model like a regular table
  • Used for Date tables, bridge tables, virtual aggregations
  • Good for: advanced modelling, helper tables
Example
HighValueOrders = FILTER(Sales, Sales[Amount] > 1000)
"Measures are like answering a question on demand. Calculated Columns are like writing the answer down ahead of time."

🔁 Filter Context vs Row Context

This is the concept that trips up most beginners — and unlocks everything once understood.

Row Context 📌

Used by Calculated Columns. Power BI evaluates the formula once for each row in the table — like Excel dragging a formula down.

Margin % = DIVIDE(Sales[Profit], Sales[Total])

This runs for every row individually — each row knows its own Sales[Profit] and Sales[Total].

Filter Context 📊

Used by Measures. The filter context is the set of filters currently applied by visuals, slicers, and report-level filters.

Total Sales = SUM(Sales[Amount])

When a slicer selects "North Region", this measure automatically sums only North Region sales.

💡 Key Insight The CALCULATE() function is powerful because it lets you modify the filter context inside a measure — adding, removing, or replacing filters programmatically. It is the most important DAX function to master after the basic aggregations.

🔥 Most Used DAX Functions

These are the functions you will use in almost every Power BI project:

SUM() / AVERAGE() / COUNT()
Aggregation
Basic aggregations that respond to filter context. The foundation of every measure.
CALCULATE()
Context Modification
The most powerful DAX function. Evaluates an expression in a modified filter context — used for YTD, % of Total, comparisons.
DIVIDE()
Safe Division
Divides two numbers safely — returns a custom result (like 0 or BLANK) instead of an error when dividing by zero.
FILTER()
Table Filtering
Returns a filtered subset of a table. Used inside CALCULATE() to define custom filter conditions.
IF() / SWITCH()
Conditional Logic
Returns different values based on a condition. SWITCH() is cleaner for multiple conditions than nested IF() statements.
RELATED()
Cross-table Lookup
Pulls a value from a related table via the model relationship. Used in calculated columns to bring in dimension attributes.
SUMX() / AVERAGEX()
Iterator Functions
Iterate row-by-row over a table and aggregate the result. Used when you need row-level calculation before summing.
TOTALYTD() / SAMEPERIODLASTYEAR()
Time Intelligence
Built-in time intelligence functions that require a marked Date table. Used for YTD, MTD, period comparisons.

⚙️ Practical DAX Examples

Here are three measures you should build in every sales report:

Total Sales
Sum all sales — adjusts to any filter.
Total Sales = SUM(Sales[Amount])
% of Total Sales
Each row's sales as a percentage of the overall total — uses CALCULATE to remove filters.
% of Total = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales)) )
Sales YTD
Cumulative sales from the start of the year — requires a marked Date table.
Sales YTD = TOTALYTD(SUM(Sales[Amount]), Dates[Date])

⚠️ Golden Rules for Writing DAX

  • Use measures over calculated columns — measures are more efficient, flexible, and respond to filter context. Use calculated columns only when you need to store a value per row.
  • Use DIVIDE() not the / operator — DIVIDE(a, b) returns BLANK instead of an error when b is zero, keeping your visuals clean.
  • Name measures clearly — "Total Sales ₹" is better than "Measure1". Clear names make reports self-documenting.
  • Format your DAX — write one argument per line for complex formulas. Use daxformatter.com to auto-format.
  • Test incrementally — build complex DAX one function at a time, verifying the result at each stage before adding more complexity.
  • Master CALCULATE() early — it is the gateway to advanced DAX. Every YTD, comparison, and overriding filter measure uses it.

🧠 Try It Yourself — Three Starter Measures

Build these three measures in your Sales dataset to practise DAX:

  1. Total Sales using SUM()
    Total Sales = SUM(Sales[Amount])
    Add this to a Card visual. Apply a Region slicer and confirm it updates dynamically.
  2. Profit Calculated Column
    In the Sales table, create a new column:
    Profit = Sales[Amount] - Sales[Cost]
    Notice it runs row-by-row and appears as a new column in the table.
  3. % of Total Sales using DIVIDE()
    % of Total = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales)), 0 )
    Add this to a table visual alongside Product Name. Confirm each row shows that product's share of total sales as a percentage.
📚 Go Deeper — Microsoft Learn Resources
Up Next in Chapter 4 4.6 Filters in Power BI — visual-level, page-level, and report-level filters, and how they interact with slicers and cross-filtering.