DAX(Data Analysis Expressions)

DAX (Data Analysis Expressions) is a formula language used in Microsoft tools like Power BI, Power Pivot, and SSAS (SQL Server Analysis Services) to create
custom calculations and expressions on data models. It is designed to work with relational data and is used to define calculated columns, measures, and
custom tables.

Key Features of DAX:

1. Calculation Language: DAX is similar to Excel formulas but optimized for use in data modeling and analytics.

2. Functions: Includes a rich library of functions such as aggregation (SUM, AVERAGE), logical (IF, SWITCH), filtering (FILTER, CALCULATE), and time
intelligence (TOTALYTD, SAMEPERIODLASTYEAR).

3. Row Context and Filter Context: DAX uses contexts to evaluate expressions depending on the data structure and filters applied.

4. Calculated Columns vs. Measures:

 Calculated Columns are computed row by row at the data refresh time.

Measures are evaluated dynamically during report interaction.

Applications of DAX:

Creating KPIs and custom metrics.

Filtering and segmenting data dynamically.

Performing time-based calculations like YTD, QoQ, and MoM.

Enhancing dashboards and reports in Power BI with interactive analysis.

In summary, DAX is essential for advanced data modeling and insightful analytics within the Microsoft data ecosystem.

Difference Between DAX And M code

Feature M Code (Power Query Formula Language) DAX (Data Analysis Expressions)

Purpose Used for data extraction, transformation, and loading Used for data modeling, calculations, and analytics

Execution Time Executes during the data load (ETL process) Executes during report viewing (after data load)

Environment Power Query Editor Power BI Data View, Report View, and Modeling View

Language Style Functional programming (case-sensitive) Excel-like expression language (case-insensitive)

Used For Data shaping, cleaning, filtering, merging Creating calculated columns, measures, KPIs

Output Type Transformed tables or queries Scalar values or calculated columns/tables

Performance Impact Affects refresh time and data load size Affects report responsiveness and interaction
Calculated Columns

Key Points:

Stored Physically: Unlike measures, calculated columns take up storage in memory.

Created Using DAX: You write DAX formulas that return a value for each row.

Uses Row Context: It calculates values based on the data in the same row.

Row Context in DAX refers to the current row that is being evaluated in a table. It allows DAX to perform calculations on a row-by-row basis.

✅ Key Points about Row Context:

Created automatically in calculated columns.

Exists in iterative functions like SUMX , AVERAGEX , FILTER , etc.

Allows access to column values in the current row without needing explicit filters.

Does not automatically filter the rest of the model—unlike filter context.

🧠 Example:
TotalCost = Sales[UnitPrice] * Sales[Quantity]

Each row uses its own UnitPrice and Quantity because of row context.

Use row context when you need calculations done per row, such as creating new columns or row-wise logic inside iterators.

Useful For:

Creating categories (e.g., High/Medium/Low sales)

Concatenating values (e.g., [First Name] & " " & [Last Name])
 Extracting parts of a field (e.g., year from date)

Example:

If you have a Sales table with Quantity and UnitPrice , you can create a calculated column for total revenue:

DAX
TotalRevenue = Sales[Quantity] * Sales[UnitPrice]

Use calculated columns when you need the result to be used in slicers, filters, or relationships.

Avoid using calculated columns when:

1. The result can be achieved with a measure, which is more efficient.

2. The column is not needed for filtering, slicing, or relationships.

3. It leads to unnecessary memory usage without improving insights.

4. The calculation can be done in Power Query (M code) before data load.

5. The output is required to be dynamic based on user interactions (use a measure).

6. You are only summarizing or aggregating values, which is better done via measures.

7. The column is used temporarily and not needed permanently in the model.

Measures in Power BI
Measures are DAX formulas used to calculate aggregated values dynamically based on user interaction with the report (like filters, slicers, visuals). Measures
are not stored in the data model; instead, they are calculated on the fly.

Example:
 Total Sales = SUM(Sales[Amount])

Difference between Measures and Calculated Columns

Feature Calculated Column Measure

Storage Stored physically in the data model Not stored; calculated during report runtime

Evaluation Time Calculated when data is loaded or refreshed Evaluated dynamically during user interaction

Used For Row-level data, relationships, filters Aggregations, KPIs, and summary-level analysis

Performance Impact Increases data size More efficient for large datasets

Supports Row Context Yes No (but can use iterators like SUMX to simulate it)

Use Case If new column is needed for filter/slicing If summary/aggregated value is needed

What are Explicit and Implicit Measures?

Type Description Example

Explicit
Created manually using DAX by the user in the data model. Total Sales = SUM(Sales[Amount])
Measure

Implicit Automatically created by Power BI when you drag a numeric field into a Dragging Sales[Amount] into a visual automatically
Measure visual (uses default aggregation like SUM, AVERAGE). creates SUM(Sales[Amount])

Note: Explicit measures are preferred in professional reports because they offer more control, reuse, and performance tuning.
⚡ Measures in Power BI
Quick Measures are predefined DAX calculations that you can create using a point-and-click interface—no need to manually write DAX formulas. Power BI
generates the DAX code for you based on the selections you make.

Key Features:

Created as explicit measures behind the scenes.

Can be edited later to learn or customize the generated DAX.

Available in the Modeling tab > New Quick Measure.

Common Quick Measure Types:

Aggregations (Average, Max, Min, etc.)

Filters (Filtered value, value for selected category)

Time Intelligence (Year-to-Date, Month-over-Month, etc.)

Running totals, Percent of total

Ranking (e.g., Rank sales by region)

Example:

You want to calculate "% of Grand Total" for Sales[Amount] .

With Quick Measures:

 Choose field: Sales[Amount]

Choose calculation: % of grand total

Power BI auto-generates:

Sales % of Total =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

Context in Power BI
In Power BI (and DAX), context is the environment in which a calculation happens.

Two main types:

Row context → deals with one row at a time (e.g., calculated columns, iterators like SUMX )

Filter context → deals with which data rows are visible when a calculation runs.

2. Filter Context
Filter context is all the filters that are applied before a calculation happens.

These filters can come from:

1. Visual filters → e.g., a slicer selecting “Year = 2024”

2. Report/page filters

3. Filters from relationships between tables

 4. DAX functions that add or remove filters ( CALCULATE , FILTER )

5. Implicit filters from visuals → e.g., a column chart bar for “Product A” only considers rows where Product = “A”

Example

Imagine a Sales table:

Product Year Sales

A 2023 100

A 2024 150

B 2024 200

If you create a card visual showing:

Total Sales = SUM(Sales[Sales])

Without any filter context → Result = 450

If a slicer filters Year = 2024 → Filter context = “Year = 2024” → Result = 350

If a chart bar shows “Product A” and “Year = 2024” → Filter context = “Product = A” AND “Year = 2024” → Result = 150

How Filter Context Works with CALCULATE

CALCULATE can change filter context.
Example:

Sales_2023 = CALCULATE(
SUM(Sales[Sales]),
Sales[Year] = 2023
)

Even if your slicer says “Year = 2024”, CALCULATE overrides it to 2023.

Important Notes
Filter context can come from multiple places and stacks together (intersection of all filters)

You can remove filter context with functions like:

REMOVEFILTERS()

ALL()

You can add/modify filter context with CALCULATE or KEEPFILTERS

💡 Think of filter context like wearing sunglasses:

If you put on red-tinted glasses (filter for Product A), everything you see will be red (only Product A data is visible to the calculation).

If you also put on blue glasses at the same time (filter for Year 2024), the overlap might let through only purple light (only rows with Product A and Year 2024).

Multiple Sources of Filter Context

Filter context can be applied from:
 Slicers (Year = 2024)

Visuals (a bar in a chart for “Product A”)

Page filters and report filters

Relationships (filtering a fact table based on a related dimension table)

DAX functions ( CALCULATE , FILTER , etc.)

All these combine → Power BI applies the intersection of all filters.

Filter Context is Static — Until You Change It

When a calculation runs, Power BI takes the existing filter context.

To change it, you must use functions like:

CALCULATE → change/add/remove filters

ALL() → remove filters on specific columns or tables

REMOVEFILTERS() → newer, removes filters cleanly

KEEPFILTERS() → add filters without overwriting existing ones

Filter Context vs Row Context

A lot of confusion comes from mixing them up:

Row context → happens in calculated columns or iterators ( SUMX , FILTER ), moves one row at a time.

Filter context → a set of rows visible to a calculation.

 Sometimes, DAX transfers row context into filter context (this is called context transition) — mostly when you use CALCULATE .

Filter Context Stacking

Multiple filters stack together (logical AND).

Example:

Slicer → Year = 2024

Chart axis → Product = A

Filter context = {Year = 2024 AND Product = A}

Advanced Filter Context Tricks

ALLSELECTED() → keeps only filters made by the user in visuals, ignores some others.

ALLEXCEPT() → removes all filters except on certain columns.

CROSSFILTER() → changes how relationships filter between tables.

DAX Syntax Basics

In Power BI, DAX (Data Analysis Expressions) is used for writing measures, calculated columns, and calculated tables.

General pattern:

<New Name> = <DAX expression>

Examples:

Total Sales = SUM(Sales[SalesAmount])

Average Price = AVERAGE(Products[UnitPrice])

Syntax rules:

Column names are in square brackets: [ColumnName]

Table names are in single quotes if they contain spaces: 'Sales Table'

Functions are in UPPERCASE by convention: SUM() , FILTER()

Arguments in functions are separated by commas ,

Text values are in double quotes "Text"

Types of DAX Operators

A. Arithmetic Operators

Operator Meaning Example Result

+ Addition 5 + 3 8

- Subtraction 10 - 4 6

* Multiplication 2 * 3 6

/ Division 10 / 2 5

^ Power 2 ^ 3 8

B. Comparison Operators

Operator Meaning Example Result

= Equal to [Rank] = 1 TRUE/FALSE

<> Not equal to [Rank] <> 1 TRUE/FALSE

> Greater than [Score] > 80 TRUE/FALSE

< Less than [Score] < 80 TRUE/FALSE

>= Greater than or equal [Score] >= 80 TRUE/FALSE

<= Less than or equal [Score] <= 80 TRUE/FALSE

C. Text Operators

Operator Meaning Example Result

& Concatenate text "Hello " & "World" "Hello World"

D. Logical Operators

Operator Meaning Example Result

&& AND [Score] > 80 && [Rank] <= 10 TRUE/FALSE

` ` OR

NOT() Negation NOT([Passed] = TRUE()) TRUE/FALSE

E. Special Operators in DAX

IN → Check if value is in a list

IF( 'Students'[Class] IN { "A", "B" }, "Allowed", "Not Allowed" )

BLANK() → Represents a blank (null) value

IF( ISBLANK(Sales[Amount]), 0, Sales[Amount] )

Operator Precedence in DAX
DAX follows a priority order when evaluating:

1. ^ (power)

2. (negation), not subtraction

3. , / (multiplication/division)

4. + , (addition/subtraction)

5. Comparison ( = , > , < , >= , <= , <> )

6. NOT

7. && (AND)

8. || (OR)

Use parentheses () to control calculation order.

Dax Function

Aggregation Functions

Function or
Syntax Example Use Cases
Formula

SUM SUM(column) SUM(Sales[SalesAmount]) Adds all numeric values in a column

AVERAGE AVERAGE(column) AVERAGE(Orders[Quantity]) Calculates the mean value of a numeric column

MIN MIN(column) MIN(Sales[SalesAmount]) Finds the smallest value in a column

MAX MAX(column) MAX(Sales[SalesAmount]) Finds the largest value in a column

COUNT COUNT(column) COUNT(Sales[SalesAmount]) Counts non-blank numeric values in a column

COUNTA COUNTA(column) COUNTA(Customers[CustomerName]) Counts all non-blank values, including text

COUNTROWS COUNTROWS(table) COUNTROWS(Sales) Counts number of rows in a table

DISTINCTCOUNT DISTINCTCOUNT(column) DISTINCTCOUNT(Customers[CustomerID]) Counts distinct (unique) values in a column

Returns the product of all numeric values in a

PRODUCT PRODUCT(column) PRODUCT(Inventory[Quantity])
column

MEDIAN MEDIAN(column) MEDIAN(Sales[SalesAmount]) Returns the median value from a column

Returns the variance of a numeric column

VAR VAR(column) VAR(Sales[SalesAmount])
(sample)

VARP VARP(column) VARP(Sales[SalesAmount]) Returns the population variance

Function or
Syntax Example Use Cases
Formula

STDEV STDEV(column) STDEV(Sales[SalesAmount]) Returns the standard deviation for a sample

STDEVP STDEVP(column) STDEVP(Sales[SalesAmount]) Returns the population standard deviation

Text Function

Function or
Syntax Example Use Cases
Formula

CONCATENATE(Customer[FirstName], " ", Joins two text strings

CONCATENATE CONCATENATE(text1, text2)
Customer[LastName]) into one

CONCATENATEX(table, expression, Joins text from multiple

CONCATENATEX(Products, Products[Name],
CONCATENATEX delimiter, [orderBy_expression],
[order])
", ") rows, with a delimiter

Returns leftmost
LEFT LEFT(text, num_chars) LEFT(Customer[Phone], 3)
characters from a string

Returns rightmost
RIGHT RIGHT(text, num_chars) RIGHT(Customer[Phone], 4)
characters from a string

Extracts text from the

MID MID(text, start_num, num_chars) MID(Customer[ID], 2, 4)
middle of a string

Returns the number of

LEN LEN(text) LEN(Customer[Name])
characters in a string

Finds the position of a

SEARCH(find_text, within_text,
SEARCH [start_num], [not_found_value])
SEARCH("@" , Customer[Email]) substring (case-
insensitive)

Finds the position of a

FIND(find_text, within_text,
FIND [start_num], [not_found_value])
FIND("A" , Customer[Name]) substring (case-
sensitive)

REPLACE(old_text, start_num, Replaces part of a string

REPLACE REPLACE(Customer[Phone], 1, 3, "XXX")
num_chars, new_text) with new text

SUBSTITUTE(text, old_text, new_text, SUBSTITUTE(Customer[Address], "Street", Replaces occurrences of

SUBSTITUTE [instance_num]) "St.") text in a string
Function or
Syntax Example Use Cases
Formula

Removes extra spaces

TRIM TRIM(text) TRIM(Customer[Name])
from text

Removes non-printable
CLEAN CLEAN(text) CLEAN(Customer[Notes])
characters

Converts text to
UPPER UPPER(text) UPPER(Customer[Name])
uppercase

Converts text to
LOWER LOWER(text) LOWER(Customer[Name])
lowercase

Capitalizes the first

PROPER PROPER(text) PROPER(Customer[Name])
letter of each word

Converts a value to text

FORMAT FORMAT(value, format_string) FORMAT(Sales[Date], "MMM YYYY")
with a specified format

Returns the Unicode

UNICHAR UNICHAR(number) UNICHAR(169)
character for a number

Returns the Unicode

UNICODE UNICODE(text) UNICODE("A")
number for a character
Logical Functions:

Function or
Syntax Example Use Cases
Formula

AND(Sales[Quantity] > 10, Sales[Discount] Returns TRUE if both

AND AND(logical1, logical2)
< 0.1) conditions are TRUE

OR(Customer[Country] = "USA", Returns TRUE if at least one

OR OR(logical1, logical2)
Customer[Country] = "Canada") condition is TRUE

Reverses the result of a

NOT NOT(logical) NOT(Customer[Active] = TRUE)
logical expression

Returns one value if

IF(logical_test, value_if_true, IF(Sales[Quantity] > 10, "Bulk",
IF value_if_false) "Single") condition is TRUE, another if
FALSE

Similar to IF, but evaluates

IF.EAGER(logical_test, IF.EAGER(Sales[Quantity] > 10, "Bulk",
IF.EAGER value_if_true, value_if_false) "Single") both results regardless of
condition

Returns a value if error

IFERROR(DIVIDE(Sales[Amount],
IFERROR IFERROR(value, value_if_error)
Sales[Quantity]), 0) occurs, otherwise the
original value

SWITCH(expression, value1, result1, SWITCH(Customer[Rating], 1, "Poor", 5, Returns a value from a list

SWITCH [value2, result2, …], [else]) "Excellent", "Average") based on a match

Returns the logical value

TRUE TRUE() IF(Sales[Quantity] > 0, TRUE(), FALSE())
TRUE

Returns the logical value

FALSE FALSE() IF(Sales[Quantity] <= 0, FALSE(), TRUE())
FALSE

ISBLANK ISBLANK(value) ISBLANK(Customer[MiddleName]) Checks if a value is blank

Function or
Syntax Example Use Cases
Formula

ISEMPTY(FILTER(Sales, Sales[Amount] > Checks if a table has no

ISEMPTY ISEMPTY(table)
1000)) rows

Checks if a value results in

ISERROR ISERROR(value) ISERROR(VALUE(Customer[Phone]))
an error

ISEVEN ISEVEN(number) ISEVEN(Customer[ID]) Checks if a number is even

ISODD ISODD(number) ISODD(Customer[ID]) Checks if a number is odd

ISNUMBER ISNUMBER(value) ISNUMBER(VALUE(Customer[Phone])) Checks if value is a number

ISTEXT ISTEXT(value) ISTEXT(Customer[Email]) Checks if value is text

ISNONTEXT ISNONTEXT(value) ISNONTEXT(Customer[Phone]) Checks if value is not text

Checks if value is logical

ISLOGICAL ISLOGICAL(value) ISLOGICAL(TRUE())
TRUE/FALSE
Date and Time Functions:

Function or
Syntax Example Use Cases
Formula

Creates a date from year, month, and day

DATE DATE(year, month, day) DATE(2025, 8, 16)
numbers

DATEDIFF(start_date, DATEDIFF(Customer[JoinDate], Calculates the difference between two dates

DATEDIFF end_date, interval) TODAY(), YEAR) in given interval (DAY, MONTH, YEAR, etc.)

DATEVALUE DATEVALUE(date_text) DATEVALUE("8/16/2025") Converts text to a date

DAY DAY(date) DAY(Sales[OrderDate]) Returns the day of the month from a date

EDATE EDATE(start_date, months) EDATE(TODAY(), -3) Shifts a date by a given number of months

Returns the last day of the month after

EOMONTH EOMONTH(start_date, months) EOMONTH(TODAY(), 1)
adding months

HOUR HOUR(datetime) HOUR(NOW()) Returns the hour from a datetime

MINUTE MINUTE(datetime) MINUTE(NOW()) Returns the minutes from a datetime

MONTH MONTH(date) MONTH(Sales[OrderDate]) Returns the month as a number

NOW NOW() NOW() Returns the current date and time

SECOND SECOND(datetime) SECOND(NOW()) Returns the seconds from a datetime

Creates a time from hour, minute, and

TIME TIME(hour, minute, second) TIME(14, 30, 0)
second numbers

TIMEVALUE TIMEVALUE(time_text) TIMEVALUE("2:30 PM") Converts text to a time

Function or
Syntax Example Use Cases
Formula

TODAY TODAY() TODAY() Returns the current date

WEEKDAY WEEKDAY(date, [return_type]) WEEKDAY(TODAY(), 2) Returns day of the week as a number

WEEKNUM WEEKNUM(date, [return_type]) WEEKNUM(TODAY(), 2) Returns the week number of a date

YEAR YEAR(date) YEAR(Sales[OrderDate]) Returns the year from a date

YEARFRAC(start_date,
YEARFRAC end_date, [basis])
YEARFRAC(DATE(2025,1,1), TODAY()) Returns fractional year between two dates
Filter Functions:

Function or
Syntax Example Use Cases
Formula

CALCULATE(SUM(Sales[SalesAmount]), Removes all filters from the

ALL ALL(tableOrColumn)
ALL(Sales)) specified table or column

Returns all values in the

CALCULATE(SUM(Sales[SalesAmount]),
ALLSELECTED ALLSELECTED(tableOrColumn)
ALLSELECTED(Sales)) current query context,
keeping outer filters

Removes all filters except

ALLEXCEPT(table, column1, CALCULATE(SUM(Sales[SalesAmount]),
ALLEXCEPT column2, …) ALLEXCEPT(Sales, Sales[Region])) those on the specified
columns

Returns all values,

ALLNOBLANKROW ALLNOBLANKROW(tableOrColumn) ALLNOBLANKROW(Products[Category]) excluding the blank row
created by relationships

Returns a one-column table

DISTINCT DISTINCT(column) DISTINCT(Sales[ProductID]) with unique values from the
column

Returns a filtered table

FILTER FILTER(table, condition) FILTER(Sales, Sales[Quantity] > 10)
based on the condition

Returns a one-column table

VALUES VALUES(column) VALUES(Sales[ProductID]) of unique values (including
blank)

CALCULATE(SUM(Sales[SalesAmount]), Removes filters from the

REMOVEFILTERS REMOVEFILTERS(tableOrColumn)
REMOVEFILTERS(Sales[ProductID])) specified table or column

CALCULATE(SUM(Sales[SalesAmount]), Adds filters without

KEEPFILTERS KEEPFILTERS(expression)
KEEPFILTERS(Sales[Quantity] > 10)) overwriting existing ones
Function or
Syntax Example Use Cases
Formula

Checks if a column is cross-

ISCROSSFILTERED ISCROSSFILTERED(columnName) ISCROSSFILTERED(Sales[ProductID])
filtered

Checks if a column is being

ISFILTERED ISFILTERED(columnName) ISFILTERED(Sales[Region])
filtered

Returns the selected value

SELECTEDVALUE(column,
SELECTEDVALUE [alternate])
SELECTEDVALUE(Sales[Region], "Multiple") if only one exists, otherwise
alternate result
Time Intelligence Functions:

Function or Formula Syntax Example Use Cases

Returns a
table of
dates from
CALCULATE(SUM(Sales[SalesAmount]), the start of
DATESYTD DATESYTD(dates[, year_end_date])
DATESYTD(Sales[OrderDate])) the year to
the max
date in
context

Returns
dates from
CALCULATE(SUM(Sales[SalesAmount]), start of
DATESMTD DATESMTD(dates)
DATESMTD(Sales[OrderDate])) month to
max date
in context

Returns
dates from
CALCULATE(SUM(Sales[SalesAmount]), start of
DATESQTD DATESQTD(dates)
DATESQTD(Sales[OrderDate])) quarter to
max date
in context

Calculates
TOTALYTD(expression, dates[, TOTALYTD(SUM(Sales[SalesAmount]), YTD value
TOTALYTD filter][, year_end_date]) Sales[OrderDate]) for the
measure

Calculates
TOTALMTD(expression, dates[, TOTALMTD(SUM(Sales[SalesAmount]), MTD value
TOTALMTD filter]) Sales[OrderDate]) for the
measure
Function or Formula Syntax Example Use Cases

Calculates
TOTALQTD(expression, dates[, TOTALQTD(SUM(Sales[SalesAmount]), QTD value
TOTALQTD filter]) Sales[OrderDate]) for the
measure

Shifts dates
by given
number of
DATEADD(dates,
DATEADD number_of_intervals, interval)
DATEADD(Sales[OrderDate], -1, YEAR) intervals
(e.g., days,
months,
years)

Returns a
parallel
PARALLELPERIOD(dates,
PARALLELPERIOD number_of_intervals, interval)
PARALLELPERIOD(Sales[OrderDate], -1, YEAR) period in
the
past/future

Returns
dates from
same
SAMEPERIODLASTYEAR SAMEPERIODLASTYEAR(dates) SAMEPERIODLASTYEAR(Sales[OrderDate]) period in
the
previous
year

Returns
dates for
PREVIOUSYEAR(dates[,
PREVIOUSYEAR year_end_date])
PREVIOUSYEAR(Sales[OrderDate]) entire
previous
year

NEXTYEAR NEXTYEAR(dates[, year_end_date]) NEXTYEAR(Sales[OrderDate]) Returns

dates for
Function or Formula Syntax Example Use Cases

entire next
year

Returns
dates for
PREVIOUSMONTH PREVIOUSMONTH(dates) PREVIOUSMONTH(Sales[OrderDate]) entire
previous
month

Returns
dates for
NEXTMONTH NEXTMONTH(dates) NEXTMONTH(Sales[OrderDate])
entire next
month

Returns the
date for
PREVIOUSDAY PREVIOUSDAY(dates) PREVIOUSDAY(Sales[OrderDate]) the
previous
day

Returns the
date for
NEXTDAY NEXTDAY(dates) NEXTDAY(Sales[OrderDate])
the next
day

Returns
dates for
PREVIOUSQUARTER PREVIOUSQUARTER(dates) PREVIOUSQUARTER(Sales[OrderDate]) entire
previous
quarter

Returns
dates for
NEXTQUARTER NEXTQUARTER(dates) NEXTQUARTER(Sales[OrderDate])
entire next
quarter
Function or Formula Syntax Example Use Cases

Returns the
STARTOFYEAR(dates[, first date of
STARTOFYEAR STARTOFYEAR(Sales[OrderDate])
year_end_date]) the year in
context

Returns the
first date of
STARTOFMONTH STARTOFMONTH(dates) STARTOFMONTH(Sales[OrderDate])
the month
in context

Returns the
first date of
STARTOFQUARTER STARTOFQUARTER(dates) STARTOFQUARTER(Sales[OrderDate])
the quarter
in context

Returns the
ENDOFYEAR(dates[, last date of
ENDOFYEAR ENDOFYEAR(Sales[OrderDate])
year_end_date]) the year in
context

Returns the
last date of
ENDOFMONTH ENDOFMONTH(dates) ENDOFMONTH(Sales[OrderDate])
the month
in context

Returns the
last date of
ENDOFQUARTER ENDOFQUARTER(dates) ENDOFQUARTER(Sales[OrderDate])
the quarter
in context

Returns
CALCULATE(SUM(Sales[SalesAmount]), dates
DATESBETWEEN(dates, start_date,
DATESBETWEEN end_date)
DATESBETWEEN(Sales[OrderDate], DATE(2024,1,1), between a
DATE(2024,3,31))) start and
end date
Function or Formula Syntax Example Use Cases

Calculates
the value
CLOSINGBALANCEMONTH(expression, CLOSINGBALANCEMONTH(SUM(Sales[SalesAmount]),
CLOSINGBALANCEMONTH dates[, filter]) Sales[OrderDate]) at the end
of the
month

Calculates
the value
CLOSINGBALANCEQUARTER(expression, CLOSINGBALANCEQUARTER(SUM(Sales[SalesAmount]),
CLOSINGBALANCEQUARTER dates[, filter]) Sales[OrderDate]) at the end
of the
quarter

Calculates
CLOSINGBALANCEYEAR(expression, CLOSINGBALANCEYEAR(SUM(Sales[SalesAmount]), the value
CLOSINGBALANCEYEAR dates[, filter]) Sales[OrderDate]) at the end
of the year

Calculates
the value
OPENINGBALANCEMONTH(expression, OPENINGBALANCEMONTH(SUM(Sales[SalesAmount]),
OPENINGBALANCEMONTH dates[, filter]) Sales[OrderDate]) at the start
of the
month

Calculates
the value
OPENINGBALANCEQUARTER(expression, OPENINGBALANCEQUARTER(SUM(Sales[SalesAmount]),
OPENINGBALANCEQUARTER dates[, filter]) Sales[OrderDate]) at the start
of the
quarter

Calculates
OPENINGBALANCEYEAR(expression, OPENINGBALANCEYEAR(SUM(Sales[SalesAmount]), the value
OPENINGBALANCEYEAR dates[, filter]) Sales[OrderDate]) at the start
of the year
Information Functions:

Function or Formula Syntax Example Use Cases

Checks if the value is

ISBLANK ISBLANK(value) ISBLANK(SUM(Sales[Amount]))
blank

Checks if the value is an

ISERROR ISERROR(value) ISERROR(DIVIDE(1, 0))
error

Returns alternate value

IFERROR IFERROR(value, alternateValue) IFERROR(1/0, 0)
if there’s an error

ISEMPTY(FILTER(Sales, Sales[Amount] > Checks if a table is

ISEMPTY ISEMPTY(table)
1000)) empty

Checks if a column is in
ISINSCOPE ISINSCOPE(columnName) ISINSCOPE(Products[Category]) the current evaluation
scope

Checks if the value is a

ISLOGICAL ISLOGICAL(value) ISLOGICAL(TRUE())
logical (TRUE/FALSE)

Checks if value is not

ISNONTEXT ISNONTEXT(value) ISNONTEXT(123)
text

Checks if value is
ISNUMBER ISNUMBER(value) ISNUMBER(123)
numeric

Checks if the current

ISSELECTEDMEASURE ISSELECTEDMEASURE(measureName) ISSELECTEDMEASURE([Total Sales]) measure is the selected
measure

Checks if a subtotal is
ISSUBTOTAL ISSUBTOTAL(columnName) ISSUBTOTAL(Sales[Amount]) being calculated for a
column
Function or Formula Syntax Example Use Cases

ISTEXT ISTEXT(value) ISTEXT("Hello") Checks if value is text

Checks if a table
CONTAINS(table, columnName, value, CONTAINS(Products, Products[ID],
CONTAINS …) 101) contains a row with
specified values

Checks if findText exists

CONTAINSSTRING(withinText,
CONTAINSSTRING findText)
CONTAINSSTRING("Power BI", "BI") within withinText (case-
insensitive)

CONTAINSSTRINGEXACT(withinText, CONTAINSSTRINGEXACT("Power BI", Same as above but

CONTAINSSTRINGEXACT findText) "BI") case-sensitive

Returns custom data for

CUSTOMDATA CUSTOMDATA() CUSTOMDATA() the current connection
user

Checks if there’s exactly

HASONEFILTER HASONEFILTER(columnName) HASONEFILTER(Sales[Region])
one filter applied

Checks if there’s exactly

HASONEVALUE HASONEVALUE(columnName) HASONEVALUE(Sales[Region]) one distinct value in
column

Checks if a row comes

ISAFTER(table, firstRow, ISAFTER(Sales, Sales[Date] =
ISAFTER orderBy_expression, …) DATE(2023,1,1), Sales[Date], ASC) after another in a
specified order
Math and Trig Functions:

Function or
Syntax Example Use Cases
Formula

ABS ABS(number) ABS(-5) Returns the absolute value of a number

ACOS ACOS(number) ACOS(0.5) Returns arccosine of a number (in radians)

ACOSH ACOSH(number) ACOSH(1.5) Returns inverse hyperbolic cosine

Returns arccotangent of a number (in

ACOT ACOT(number) ACOT(1)
radians)

ACOTH ACOTH(number) ACOTH(2) Returns inverse hyperbolic cotangent

ASIN ASIN(number) ASIN(0.5) Returns arcsine of a number (in radians)

ASINH ASINH(number) ASINH(1) Returns inverse hyperbolic sine

ATAN ATAN(number) ATAN(1) Returns arctangent of a number (in radians)

ATANH ATANH(number) ATANH(0.5) Returns inverse hyperbolic tangent

Rounds number up to nearest multiple of

CEILING CEILING(number, significance) CEILING(4.3, 1)
significance

Returns number of combinations without

COMBIN COMBIN(number, number_chosen) COMBIN(5, 2)
repetition

Returns number of combinations with

COMBINA COMBINA(number, number_chosen) COMBINA(5, 2)
repetition

COS COS(number) COS(PI()) Returns cosine of an angle (in radians)

Function or
Syntax Example Use Cases
Formula

COSH COSH(number) COSH(1) Returns hyperbolic cosine

COT COT(number) COT(1) Returns cotangent of an angle

COTH COTH(number) COTH(1) Returns hyperbolic cotangent

CURRENCY CURRENCY(value) CURRENCY(123.456) Converts to currency data type

DEGREES DEGREES(angle) DEGREES(PI()) Converts radians to degrees

DIVIDE(numerator, denominator, Safe division, returns alternate result if

DIVIDE DIVIDE(5, 0, 0)
alternateResult) division by zero

EVEN EVEN(number) EVEN(3) Rounds number up to nearest even integer

EXP EXP(number) EXP(1) Returns e raised to a power

FACT FACT(number) FACT(5) Returns factorial of a number

FACTDOUBLE FACTDOUBLE(number) FACTDOUBLE(5) Returns double factorial

Rounds down to nearest multiple of

FLOOR FLOOR(number, significance) FLOOR(4.3, 1)
significance

GCD GCD(number1, number2) GCD(8, 12) Returns greatest common divisor

INT INT(number) INT(4.7) Returns integer part of a number

ISO.CEILING(4.3,
ISO.CEILING ISO.CEILING(number, significance)
1) CEILING but follows ISO rules

LCM LCM(number1, number2) LCM(4, 6) Returns least common multiple

Function or
Syntax Example Use Cases
Formula

LN LN(number) LN(10) Returns natural logarithm

LOG LOG(number, base) LOG(8, 2) Returns logarithm with specified base

LOG10 LOG10(number) LOG10(100) Returns base-10 logarithm

MOD MOD(number, divisor) MOD(10, 3) Returns remainder after division

MROUND MROUND(number, multiple) MROUND(10, 3) Rounds to nearest multiple

ODD ODD(number) ODD(2) Rounds number up to nearest odd integer

PI PI() PI() Returns value of π

POWER POWER(number, power) POWER(2, 3) Returns a number raised to a power

PRODUCT PRODUCT(number1, number2, …) PRODUCT(2, 3, 4) Multiplies numbers together

QUOTIENT QUOTIENT(numerator, denominator) QUOTIENT(10, 3) Returns integer portion of division

RADIANS RADIANS(angle) RADIANS(180) Converts degrees to radians

RAND RAND() RAND() Returns random number between 0 and 1

RANDBETWEEN(1, Returns random integer between two

RANDBETWEEN RANDBETWEEN(bottom, top)
100) numbers

ROUND ROUND(number, num_digits) ROUND(4.567, 2) Rounds to specified number of digits

ROUNDDOWN(4.567,
ROUNDDOWN ROUNDDOWN(number, num_digits)
2) Always rounds down

ROUNDUP ROUNDUP(number, num_digits) ROUNDUP(4.567, 2) Always rounds up

Function or
Syntax Example Use Cases
Formula

SIGN SIGN(number) SIGN(-5) Returns -1, 0, or 1 depending on sign

SQRT SQRT(number) SQRT(9) Returns square root

SQRTPI SQRTPI(number) SQRTPI(2) Returns square root of (number × π)

TRUNC TRUNC(number, num_digits) TRUNC(4.567, 2) Truncates to given number of decimal places

Statistical Functions:

Function or Formula Syntax Example Use Cases

Estimates distinct
APPROXIMATEDISTINCTCOUNT APPROXIMATEDISTINCTCOUNT(column) APPROXIMATEDISTINCTCOUNT(Sales[CustomerID]) count faster than
DISTINCTCOUNT
for large datasets

Returns beta
BETA.DIST(x, alpha, beta,
BETA.DIST cumulative, [A], [B])
BETA.DIST(2, 8, 10, TRUE) distribution
probability

Returns inverse
BETA.INV(probability, alpha, of beta
BETA.INV BETA.INV(0.5, 8, 10)
beta, [A], [B]) cumulative
probability

Chi-squared
CHISQ.DIST(x, deg_freedom,
CHISQ.DIST cumulative)
CHISQ.DIST(18.307, 10, TRUE) distribution
probability

Right-tail chi-
CHISQ.DIST.RT CHISQ.DIST.RT(x, deg_freedom) CHISQ.DIST.RT(18.307, 10) squared
distribution

CHISQ.INV(probability, Inverse of left-tail

CHISQ.INV CHISQ.INV(0.95, 10)
deg_freedom) chi-squared

CHISQ.INV.RT(probability, Inverse of right-

CHISQ.INV.RT CHISQ.INV.RT(0.05, 10)
deg_freedom) tail chi-squared

Confidence
CONFIDENCE.NORM(alpha, interval using
CONFIDENCE.NORM CONFIDENCE.NORM(0.05, 1, 50)
standard_dev, size) normal
distribution
Function or Formula Syntax Example Use Cases

Confidence
CONFIDENCE.T(alpha, interval using
CONFIDENCE.T CONFIDENCE.T(0.05, 1, 50)
standard_dev, size) Student's t-
distribution

Population
COVARIANCE.P COVARIANCE.P(columnX, columnY) COVARIANCE.P(Sales[Price], Sales[Quantity])
covariance

Sample
COVARIANCE.S COVARIANCE.S(columnX, columnY) COVARIANCE.S(Sales[Price], Sales[Quantity])
covariance

Sum of squared
DEVSQ DEVSQ(column) DEVSQ(Sales[Amount]) deviations from
mean

Exponential
EXPON.DIST(x, lambda,
EXPON.DIST cumulative)
EXPON.DIST(0.5, 10, TRUE) distribution
probability

F.DIST(x, deg_freedom1, F probability

F.DIST F.DIST(2.45, 5, 2, TRUE)
deg_freedom2, cumulative) distribution

F.DIST.RT(x, deg_freedom1, Right-tail F

F.DIST.RT F.DIST.RT(2.45, 5, 2)
deg_freedom2) distribution

F.INV(probability, deg_freedom1, Inverse of F

F.INV F.INV(0.95, 5, 2)
deg_freedom2) distribution

F.INV.RT(probability, Inverse of right-

F.INV.RT F.INV.RT(0.05, 5, 2)
deg_freedom1, deg_freedom2) tail F distribution

Fisher
FISHER FISHER(x) FISHER(0.5)
transformation
Function or Formula Syntax Example Use Cases

Inverse Fisher
FISHERINV FISHERINV(y) FISHERINV(0.5493)
transformation

FORECAST.ETS(target_date, Forecasts using

values, timeline, [seasonality], FORECAST.ETS(DATE(2025,12,31),
FORECAST.ETS [data_completion], Sales[Amount], Sales[Date]) exponential
[aggregation]) smoothing

Returns
confidence
FORECAST.ETS.CONFINT FORECAST.ETS.CONFINT(...) (same style as above)
interval for
forecast

Detects
FORECAST.ETS.SEASONALITY FORECAST.ETS.SEASONALITY(...) (same style) seasonality from
forecast

Returns statistical
FORECAST.ETS.STAT FORECAST.ETS.STAT(...) (same style) info about
forecast

FORECAST.LINEAR(x, known_y's, FORECAST.LINEAR(12, Sales[Amount], Linear regression

FORECAST.LINEAR known_x's) Sales[Month]) forecast

Gamma function
GAMMA GAMMA(x) GAMMA(5)
value

GAMMA.DIST(x, alpha, beta, Gamma

GAMMA.DIST GAMMA.DIST(2, 3, 2, TRUE)
cumulative) distribution

Inverse of
GAMMA.INV(probability, alpha,
GAMMA.INV beta)
GAMMA.INV(0.5, 3, 2) gamma
distribution

Natural log of
GAMMALN GAMMALN(x) GAMMALN(5)
gamma function
Function or Formula Syntax Example Use Cases

Same as above
GAMMALN.PRECISE GAMMALN.PRECISE(x) GAMMALN.PRECISE(5) with higher
precision

Standard normal
cumulative
GAUSS GAUSS(z) GAUSS(2)
distribution from
0 to z

GEOMEAN GEOMEAN(column) GEOMEAN(Sales[Amount]) Geometric mean

HARMEAN HARMEAN(column) HARMEAN(Sales[Amount]) Harmonic mean

Max value based

MAXX MAXX(table, expression) MAXX(Sales, Sales[Amount])
on expression

Min value based

MINX MINX(table, expression) MINX(Sales, Sales[Amount])
on expression

Median of
MEDIANX MEDIANX(table, expression) MEDIANX(Sales, Sales[Amount])
expression values

Most frequent
MODE.SNGL MODE.SNGL(column) MODE.SNGL(Sales[Amount])
value

Normal
NORM.DIST(x, mean, standard_dev,
NORM.DIST cumulative)
NORM.DIST(42, 50, 10, TRUE) distribution
probability

NORM.INV(probability, mean, Inverse normal

NORM.INV NORM.INV(0.95, 50, 10)
standard_dev) distribution

Standard normal
NORM.S.DIST NORM.S.DIST(z, cumulative) NORM.S.DIST(1.333, TRUE)
distribution
Function or Formula Syntax Example Use Cases

Inverse standard
NORM.S.INV NORM.S.INV(probability) NORM.S.INV(0.95) normal
distribution

Exclusive
PERCENTILE.EXC PERCENTILE.EXC(column, k) PERCENTILE.EXC(Sales[Amount], 0.9)
percentile

Inclusive
PERCENTILE.INC PERCENTILE.INC(column, k) PERCENTILE.INC(Sales[Amount], 0.9)
percentile

Exclusive
PERCENTRANK.EXC PERCENTRANK.EXC(column, x) PERCENTRANK.EXC(Sales[Amount], 500)
percentile rank

Inclusive
PERCENTRANK.INC PERCENTRANK.INC(column, x) PERCENTRANK.INC(Sales[Amount], 500)
percentile rank

Poisson
POISSON.DIST(x, mean,
POISSON.DIST cumulative)
POISSON.DIST(2, 5, TRUE) distribution
probability

QUARTILE.EXC QUARTILE.EXC(column, quart) QUARTILE.EXC(Sales[Amount], 1) Exclusive quartile

QUARTILE.INC QUARTILE.INC(column, quart) QUARTILE.INC(Sales[Amount], 1) Inclusive quartile

RANK.EQ RANK.EQ(number, column, [order]) RANK.EQ(500, Sales[Amount], 0) Rank with ties

RANKX(table, expression, Rank values with

RANKX RANKX(Sales, Sales[Amount])
[value], [order], [ties]) options

R-squared
RSQ RSQ(columnY, columnX) RSQ(Sales[Amount], Sales[Quantity])
correlation

Skewness of
SKEW SKEW(column) SKEW(Sales[Amount])
distribution
Function or Formula Syntax Example Use Cases

SLOPE SLOPE(columnY, columnX) SLOPE(Sales[Amount], Sales[Quantity]) Regression slope

Population
STDEV.P STDEV.P(column) STDEV.P(Sales[Amount]) standard
deviation

Sample standard
STDEV.S STDEV.S(column) STDEV.S(Sales[Amount])
deviation

Std dev of
STDEVX.P STDEVX.P(table, expression) STDEVX.P(Sales, Sales[Amount]) expression
(population)

Std dev of
STDEVX.S STDEVX.S(table, expression) STDEVX.S(Sales, Sales[Amount]) expression
(sample)

T.DIST(x, deg_freedom, Student’s t-

T.DIST T.DIST(1.96, 10, TRUE)
cumulative) distribution

Two-tailed t-
T.DIST.2T T.DIST.2T(x, deg_freedom) T.DIST.2T(1.96, 10)
distribution

Right-tail t-
T.DIST.RT T.DIST.RT(x, deg_freedom) T.DIST.RT(1.96, 10)
distribution

Inverse of t-
T.INV T.INV(probability, deg_freedom) T.INV(0.975, 10)
distribution

Inverse two-
T.INV.2T(probability,
T.INV.2T deg_freedom)
T.INV.2T(0.05, 10) tailed t-
distribution

Population
VAR.P VAR.P(column) VAR.P(Sales[Amount])
variance
Function or Formula Syntax Example Use Cases

VAR.S VAR.S(column) VAR.S(Sales[Amount]) Sample variance

Variance of
VARX.P VARX.P(table, expression) VARX.P(Sales, Sales[Amount]) expression
(population)

Variance of
VARX.S VARX.S(table, expression) VARX.S(Sales, Sales[Amount]) expression
(sample)
Table Functions:

Function or Formula Syntax Example Use Cases

Adds
ADDCOLUMNS(table, name, expression, ADDCOLUMNS(Sales, "Profit", Sales[Revenue] calculated
ADDCOLUMNS …) - Sales[Cost]) columns to a
table

Returns
Cartesian
CROSSJOIN CROSSJOIN(table1, table2, …) CROSSJOIN(Products, Customers)
product of
tables

Returns
unique values
DISTINCT DISTINCT(column) DISTINCT(Sales[Region])
from a
column

Returns rows
EXCEPT(DISTINCT(Sales[Region]),
EXCEPT EXCEPT(table1, table2)
DISTINCT(Returns[Region])) in table1 not
in table2

Returns
subset of
FILTER FILTER(table, expression) FILTER(Sales, Sales[Amount] > 1000) table
matching
condition

For each row

in table1,
GENERATE GENERATE(table1, table2) GENERATE(Customers, Sales) returns table2
and
combines

GENERATEALL GENERATEALL(table1, table2) GENERATEALL(Customers, Sales) Like

GENERATE,
Function or Formula Syntax Example Use Cases

but includes
rows with no
matches

Returns rows
INTERSECT(DISTINCT(Sales[Region]),
INTERSECT INTERSECT(table1, table2)
DISTINCT(Targets[Region])) common to
both tables

Inner join
using
NATURALINNERJOIN NATURALINNERJOIN(table1, table2) NATURALINNERJOIN(Products, Categories)
common
columns

Left outer
NATURALLEFTOUTERJOIN(table1, join using
NATURALLEFTOUTERJOIN NATURALLEFTOUTERJOIN(Customers, Sales)
table2) common
columns

Creates table
SELECTCOLUMNS(table, name, SELECTCOLUMNS(Sales, "Customer",
SELECTCOLUMNS expression, …) Sales[Customer], "Total", Sales[Amount]) with specific
columns

Groups and
SUMMARIZE(table, groupBy_columnName, SUMMARIZE(Sales, Sales[Region], "Total
SUMMARIZE [name, expression], …) Sales", SUM(Sales[Amount])) summarizes
data

Groups and
SUMMARIZECOLUMNS(groupBy_columnName, SUMMARIZECOLUMNS(Products[Category], summarizes
SUMMARIZECOLUMNS [name, expression], …) "Total", SUM(Sales[Amount])) without row
context

Combines
UNION(DISTINCT(Sales[Region]), rows from
UNION UNION(table1, table2)
DISTINCT(Returns[Region])) multiple
tables
Function or Formula Syntax Example Use Cases

Returns
unique values
VALUES VALUES(column) VALUES(Sales[Region]) from a
column, plus
BLANK if any

Returns top N
TOPN(n_value, table,
TOPN orderBy_expression, [order])
TOPN(5, Sales, Sales[Amount], DESC) rows by sort
order

Groups table
GROUPBY(table, groupBy_columnName, GROUPBY(Sales, Sales[Region], "MaxAmt",
GROUPBY [name, expression], …) MAXX(CURRENTGROUP(), Sales[Amount])) and applies
aggregation

DATATABLE(columnName, dataType, { DATATABLE("Month", STRING, { {"Jan"}, Creates static

DATATABLE {value}, {value}, … }) {"Feb"} }) table in DAX

Adds missing
items to a
ADDMISSINGITEMS ADDMISSINGITEMS(table) ADDMISSINGITEMS(VALUES(Products[Category]))
table for
completeness
Parent and Child Functions:

Function or
Syntax Example Use Cases
Formula

Returns a delimited text

PATH(child_column,
PATH parent_column)
PATH(Employee[ID], Employee[ManagerID]) string with IDs of all
ancestors of a node

PATHCONTAINS(PATH(Employee[ID], Checks if a specific ID exists

PATHCONTAINS PATHCONTAINS(path, item)
Employee[ManagerID]), "5") in a path

PATHITEM(path, position, PATHITEM(PATH(Employee[ID], Returns the item at a given

PATHITEM [type]) Employee[ManagerID]), 2, INTEGER) position in a path

Returns item at a position

PATHITEMREVERSE(path, PATHITEMREVERSE(PATH(Employee[ID],
PATHITEMREVERSE position, [type]) Employee[ManagerID]), 1, INTEGER) counting from the end of
the path

PATHLENGTH(PATH(Employee[ID], Returns number of items in

PATHLENGTH PATHLENGTH(path)
Employee[ManagerID])) a path
Other Functions:

Function or Formula Syntax Example Use Cases

Returns a blank
value; often used
BLANK BLANK() IF(SUM(Sales[Amount]) = 0, BLANK())
to hide zeros or
missing data

IF(SUM(Sales[Amount]) < 0, Throws a custom

ERROR ERROR("message")
ERROR("Negative sales not allowed")) error message

Returns the value

LOOKUPVALUE(result_column, from a column for
LOOKUPVALUE(Product[Price],
LOOKUPVALUE search_column, search_value[,
Product[ID], 1001) the row where all
search_column2, search_value2], …) given search
conditions are true

Stores a value in a
VAR variableName = expression VAR totalSales = SUM(Sales[Amount]) variable for reuse
VAR … RETURN RETURN result RETURN totalSales / 1000 in the same
expression

UNION(VALUES(Product[Category]), Combines tables by

UNION UNION(table1, table2 [, tableN])
VALUES(Product[SubCategory])) rows

Returns rows
INTERSECT(VALUES(Sales[CustomerID]),
INTERSECT INTERSECT(table1, table2)
VALUES(Returns[CustomerID])) present in both
tables

Returns rows
EXCEPT(VALUES(Sales[CustomerID]), present in first
EXCEPT EXCEPT(table1, table2)
VALUES(Returns[CustomerID])) table but not in
second

NATURALINNERJOIN NATURALINNERJOIN(table1, table2) NATURALINNERJOIN(Orders, Customers) Joins two tables

based on columns
Function or Formula Syntax Example Use Cases

with same names

(inner join)

Joins two tables

NATURALLEFTOUTERJOIN(table1, NATURALLEFTOUTERJOIN(Orders, based on same-
NATURALLEFTOUTERJOIN table2) Customers) named columns
(left join)
Variables in DAX ( VAR keyword)
Variables let you store a value or table temporarily inside a DAX expression and reuse it without recalculating it multiple times.

Syntax

VAR <variable_name> = <expression>

VAR <variable_name2> = <expression2>
RETURN <final_expression>

Example

VAR TotalSales = SUM(Sales[Amount])

VAR TotalQuantity = SUM(Sales[Quantity])
RETURN
TotalSales / TotalQuantity

What happens here:

First, TotalSales is calculated and stored.

Then TotalQuantity is calculated and stored.

Finally, DAX returns sales per quantity using those stored values.
Why Use Variables?

Performance → DAX calculates the variable once and reuses it.

Readability → Breaks a complex formula into steps.

Debugging → Easier to test intermediate results.

Iterators in DAX
Iterators are DAX functions that loop through a table row-by-row, evaluate an expression for each row, and then aggregate the results.

They are often called the X functions because most of them end with an X (e.g., SUMX , AVERAGEX , RANKX ).

They are more flexible than simple aggregators like SUM or AVERAGE because you can perform a custom calculation on each row before aggregation.

How They Work

1. Take a table as the first argument.

2. Evaluate the expression for each row in that table.

3. Return a single value after aggregating all row results.

Iterator Functions:

A. Aggregation Iterators
These are the X versions of basic aggregators:
 Iterator Syntax Purpose

SUMX SUMX(Table, Expression) Sums the expression for each row.

AVERAGEX AVERAGEX(Table, Expression) Averages the expression per row.

MINX MINX(Table, Expression) Finds smallest value after row-by-row calculation.

MAXX MAXX(Table, Expression) Finds largest value after row-by-row calculation.

PRODUCTX PRODUCTX(Table, Expression) Multiplies values of the expression for each row.

MEDIANX MEDIANX(Table, Expression) Finds the median of the calculated values.

B. Counting Iterators

Iterator Syntax Purpose

COUNTX COUNTX(Table, Expression) Counts numeric results of the expression per row.

COUNTAX COUNTAX(Table, Expression) Counts all non-blank values from the expression per row.

DISTINCTCOUNTX DISTINCTCOUNTX(Table, Expression) Counts distinct results from the expression per row.
C. Ranking Iterators

Iterator Syntax Purpose

RANKX RANKX(Table, Expression, [Value], [Order], [Ties]) Ranks items based on expression result.

D. Filtering & Table Iterators

These iterate but also return tables.

Iterator Syntax Purpose

FILTER FILTER(Table, Condition) Returns a table of rows meeting a condition (iterates over each row).

ADDCOLUMNS ADDCOLUMNS(Table, Name, Expression, ...) Adds calculated columns after iterating over each row.

SELECTCOLUMNS SELECTCOLUMNS(Table, Name, Expression, ...) Creates a table with new columns after iteration.

GENERATE GENERATE(Table1, Table2) Iterates through each row of Table1 and returns Table2 for each.

GENERATEALL GENERATEALL(Table1, Table2) Like GENERATE but keeps unmatched rows.

CROSSJOIN CROSSJOIN(Table1, Table2) Produces all combinations by iterating through both tables.

ADDMISSINGITEMS ADDMISSINGITEMS(Table) Iterates to fill in missing combinations.

E. Time Intelligence Iterators

These iterate over date ranges.
Iterator Syntax Purpose

DATESBETWEEN DATESBETWEEN(DateColumn, StartDate, EndDate) Iterates over dates between two points.

DATESINPERIOD(DateColumn, StartDate, NumberOfIntervals,

DATESINPERIOD Interval) Iterates over a fixed time window.

DATESYTD / QTD / DATESYTD(DateColumn, [YearEndDate])

Iterates over Year-to-Date/Quarter-to-Date/Month-
MTD to-Date ranges.