DAX: The Complete Guide

DAX: The Complete Guide – Functions, Concepts & Practical Examples

Master Data Analysis Expressions (DAX) — the powerful formula language behind Microsoft Power BI, Excel Power Pivot, and SQL Server Analysis Services Tabular models. This comprehensive guide covers core concepts, essential functions, and real-world examples.

Table of Contents

  • 1. What is DAX?
  • 2. Calculated Columns vs. Measures
  • 3. Row Context & Filter Context
  • 4. Essential DAX Functions
  • 5. Aggregation Functions
  • 6. Logical Functions
  • 7. Text Functions
  • 8. Date & Time Functions
  • 9. Time Intelligence Functions
  • 10. Iterator (X) Functions

1. What is DAX?

Data Analysis Expressions (DAX) is a library of functions and operators used to build formulas and expressions in Microsoft’s tabular data modeling tools. It powers dynamic calculations in:

  • Power BI (Desktop & Service)
  • Excel Power Pivot
  • SQL Server Analysis Services (SSAS) Tabular

DAX enables analysts and data professionals to create calculated columns, measures, and calculated tables for advanced analytics, time-based comparisons, and contextual aggregations.

2. Calculated Columns vs. Measures

Calculated Column

Computed row-by-row during data refresh. Results are stored in the model and consume memory.

Sales[Profit] = Sales[Revenue] - Sales[Cost]

Measure

Evaluated dynamically at query time based on current filter context (slicers, visuals, rows/columns in a matrix).

Total Sales = SUM(Sales[Revenue])
Key Difference: Measures respond to user interactions (filters, slicers, drill-downs). Calculated columns are static and do not react to visual context.

3. Row Context & Filter Context

Row Context

Automatically created when a formula evaluates an expression for each row in a table — common in calculated columns and iterator functions (e.g., SUMX).

Filter Context

Defined by the current selection in visuals, slicers, filters, or explicitly modified using CALCULATE().

Sales Last Year = CALCULATE( SUM(Sales[Revenue]), SAMEPERIODLASTYEAR('Date'[Date]) )

4. Essential DAX Functions

4.1 CALCULATE() — The Most Powerful Function

Modifies or replaces the existing filter context. It is the cornerstone of nearly all advanced DAX expressions.

Profit Last Year = CALCULATE( SUM(Sales[Profit]), YEAR(Sales[Date]) = YEAR(TODAY()) - 1 )

4.2 FILTER() — Row-Level Filtering

Returns a table filtered row-by-row. Used inside CALCULATE for complex conditions.

High Value Sales = CALCULATE( SUM(Sales[Revenue]), FILTER(Sales, Sales[Revenue] > 1000) )

5. Aggregation Functions

Perform summary calculations over a column or expression.

  • SUM() – Total
  • AVERAGE() – Mean
  • MIN(), MAX() – Extremes
  • COUNT(), COUNTA() – Count values
  • COUNTROWS() – Count table rows
  • DIVIDE(numerator, denominator, [alternate_result]) – Safe division
Average Unit Price = AVERAGE(Sales[Unit Price])

6. Logical Functions

  • IF(logical_test, value_if_true, value_if_false)
  • SWITCH(expression, value1, result1, ..., [default])
  • AND(), OR()
  • NOT()
Sales Category = SWITCH( TRUE(), Sales[Revenue] > 1000, "High", Sales[Revenue] > 500, "Medium", "Low" )

7. Text Functions

  • CONCATENATE(text1, text2) or & operator
  • LEFT(), RIGHT(), MID()
  • UPPER(), LOWER(), PROPER()
  • FORMAT(value, format_string)
Formatted Month = FORMAT('Date'[Date], "MMMM yyyy")

8. Date & Time Functions

  • TODAY(), NOW()
  • YEAR(), MONTH(), DAY()
  • DATE(year, month, day)
  • DATEDIFF(start_date, end_date, interval)
Shipping Delay (Days) = DATEDIFF(Sales[Order Date], Sales[Ship Date], DAY)

9. Time Intelligence Functions

Require a properly marked Date table with continuous dates.

  • SAMEPERIODLASTYEAR()
  • DATEADD()
  • PARALLELPERIOD()
  • TOTALYTD(), CLOSINGBALANCEMONTH()
Sales YoY % = VAR CurrentSales = [Total Sales] VAR LastYearSales = [Sales Last Year] RETURN DIVIDE(CurrentSales - LastYearSales, LastYearSales)

10. Iterator (X) Functions

Iterate over a table, evaluate an expression for each row, then aggregate results. Create row context manually.

  • SUMX(table, expression)
  • AVERAGEX(), MINX(), MAXX()
  • COUNTX(), FILTER() with iterators
Total Profit (Row-Level) = SUMX( Sales, Sales[Unit Price] * Sales[Quantity] - Sales[Unit Cost] * Sales[Quantity] )
Best Practice: Use iterator functions when logic cannot be expressed with standard aggregations (e.g., weighted averages, conditional sums).

Comments

Post a Comment

Popular posts from this blog

Automate Blog Content Creation with n8n and Grok 3 API

Automate Blog Content Creation with n8n and Grok 3 API Introduction n8n is an open-source automation tool that connects apps to streamline tasks. This tutorial guides you through creating a workflow to automate blog post creation for Google Blogger using the Grok 3 API for content generation, Pexels for images, and Google Sheets for storage. The workflow ensures niche-relevant content and uses n8n’s free tier. Prerequisites n8n account (free at n8n.io ) Grok API key ( x.ai/api ) Pexels API key ( pexels.com/api ) Google account for Sheets and Blogger Google Sheet with columns: Title, Content, Meta Description, Image URL, Status Workflow Overview The workflow: Schedules automatic runs Sets niche keywords Generates a blog post topic Drafts a detailed post Fetches a relevant image Saves data to Google Sheets Handles e...
Read more

Neuro-Symbolic Integration: Enhancing LLMs with Knowledge Graphs

Image
Neuro-Symbolic Integration: Enhancing LLMs with Knowledge Graphs Abstract Large Language Models (LLMs) have revolutionized natural language processing, achieving remarkable success in tasks like text generation and question answering. However, their reasoning capabilities are constrained by hallucinations—generating plausible but factually incorrect outputs—and limited parametric memory, which hampers their ability to maintain context over long interactions or perform complex multi-step reasoning. This article synthesizes insights from 2024-2025 surveys on neuro-symbolic artificial intelligence, focusing on integrating LLMs with Knowledge Graphs (KGs) to enhance factual grounding, reasoning, and knowledge management in real-world applications. We explore methodologies for knowledge extraction, representation, reasoning, and dynamic updating, emphasizing bidirectional synergies where LLMs automate KG construction and KGs improve LLM reasoni...
Read more

Understanding and Using the Generalized Pareto Distribution (GPD)

Understanding and Using the Generalized Pareto Distribution (GPD) The Generalized Pareto Distribution (GPD) is a probability distribution used in Extreme Value Theory to model values that exceed a certain high threshold. It is widely used in finance, insurance, hydrology, and environmental science. 📘 What is the GPD? The GPD models the distribution of excess values over a threshold. That is, if we set a threshold u , the GPD models the distribution of X − u | X > u . 🔣 Probability Density Function (PDF) f(x) = (1 / σ) * (1 + ξ * x / σ)^(-1/ξ - 1) ξ : Shape parameter (controls the heaviness of the tail) σ : Scale parameter (spread) Support: x ≥ 0 if ξ ≥ 0 ; 0 ≤ x ≤ -σ/ξ if ξ < 0 🛠️ Fitting GPD to Synthetic Insurance Claims (Python Example) Let’s simulate a small dataset of insurance claims, set a threshold, and fit a Generalized Pareto Distribution using scipy . 🔢 Step 1: Simulate Data import numpy as np import matplotlib.py...
Read more