DAX Functions

Complete Reference Guide

Using the Superstore Dataset

Power BI | DAX | Data Analysis

Covers 10 Function Categories · 50+ Functions · Practical Business Examples

Superstore Dataset — Key Fields Reference

Orders Table Products Table Customers Table

Order ID, Order Date Product ID, Product Name Customer ID, Customer Name
Ship Date, Ship Mode Category, Sub-Category Segment
Sales, Quantity, Profit Manufacturer
Discount, Region
Segment, State, City

📊 Aggregation Functions
Aggregation functions summarise data across rows or tables. They are the backbone of most Power BI measures
and KPI cards.

SUM Aggregation

Syntax SUM(<column>)

Description Adds all numeric values in a column, ignoring blanks.

Example Total Sales = SUM(Orders[Sales])

Output Returns the sum of all sales amounts — e.g. $2,297,200.86

Use Case Used on KPI cards to display total revenue, total quantity sold, or total
discount given.

DAX Reference Guide · Superstore Dataset · Page 1

SUMX Aggregation — Iterator

Syntax SUMX(<table>, <expression>)

Description Iterates row-by-row over a table and sums the result of an expression.
Essential for row-level calculations.
Example Profit After Discount = SUMX(Orders, Orders[Sales] * (1 -
Orders[Discount]) - Orders[Profit])

Output Calculates the net profit after applying per-row discounts — a row-context
aware sum.
Use Case Use when you need to compute something per row before aggregating, such
as revenue × margin for each order line.

AVERAGE Aggregation

Syntax AVERAGE(<column>)

Description Returns the arithmetic mean of all values in a numeric column.

Example Avg Order Value = AVERAGE(Orders[Sales])

Output Returns average order sales — e.g. $229.86

Use Case Tracking average transaction value to identify high-value customer segments.

AVERAGEX Aggregation — Iterator

Syntax AVERAGEX(<table>, <expression>)

Description Iterates a table, evaluates an expression per row, and returns the average.
Example Avg Profit Margin % = AVERAGEX(Orders, DIVIDE(Orders[Profit],
Orders[Sales]))

Output Returns average profit margin per order line — e.g. 12.47%
Use Case Computing per-product or per-region average margin rates without pre-
calculating a helper column.

COUNT Aggregation

Syntax COUNT(<column>)

Description Counts the number of rows containing non-blank numeric values in a column.
Example Count of Orders = COUNT(Orders[Sales])

Output Returns the number of order rows with a sales value — e.g. 9,994
Use Case Quick count of transactions; useful for order volume dashboards.

COUNTA Aggregation

DAX Reference Guide · Superstore Dataset · Page 2

Syntax COUNTA(<column>)

Description Counts the number of non-blank values in a column (works on text and
numbers).
Example Unique Orders = COUNTA(Orders[Order ID])

Output Counts all order IDs including text-format IDs — e.g. 9,994
Use Case Counting rows in columns that contain text identifiers like Order ID or
Customer Name.

COUNTROWS Aggregation

Syntax COUNTROWS([<table>])

Description Returns the number of rows in a table or a filtered table expression.

Example Orders per Region = COUNTROWS(FILTER(Orders, Orders[Region] =
"West"))

Output Returns count of orders from the West region — e.g. 3,203
Use Case Ideal for counting after applying filters; used extensively in measure
branching and KPIs.

DISTINCTCOUNT Aggregation

Syntax DISTINCTCOUNT(<column>)

Description Returns the count of distinct, non-blank values in a column.

Example Unique Customers = DISTINCTCOUNT(Orders[Customer ID])

Output Returns number of unique customers — e.g. 793

Use Case Used in customer analytics to measure reach, unique products ordered, or
distinct regions served.

MIN / MAX Aggregation

Syntax MIN(<column>) / MAX(<column>)

Description Returns the smallest or largest value in a column (works on dates and
numbers).
Example First Order Date = MIN(Orders[Order Date]) | Last Order Date =
MAX(Orders[Order Date])

Output MIN → 03-Jan-2014 | MAX → 30-Dec-2017

Use Case Determining the date range of your dataset, the lowest/highest sale in a
period, or earliest ship date.

DAX Reference Guide · Superstore Dataset · Page 3

📅 Time Intelligence Functions
Time intelligence functions work with a dedicated Date table (marked as a Date Table in Power BI) to perform
period-over-period and cumulative calculations.

TOTALYTD Time Intelligence

Syntax TOTALYTD(<expression>, <dates> [, <filter>] [,

<year_end_date>])

Description Evaluates an expression for the year-to-date period based on the current
filter context.
Example Sales YTD = TOTALYTD(SUM(Orders[Sales]), 'Date'[Date])

Output For a month slicer set to June 2017, returns cumulative sales from Jan–Jun
2017 — e.g. $336,492
Use Case Revenue YTD cards, KPI comparisons against annual targets, and running
total line charts.

SAMEPERIODLASTYEAR Time Intelligence

Syntax SAMEPERIODLASTYEAR(<dates>)

Description Returns a table of dates corresponding to the same period in the previous
year. Typically wrapped in CALCULATE.
Example Sales LY = CALCULATE(SUM(Orders[Sales]),
SAMEPERIODLASTYEAR('Date'[Date]))

Output For a filter on Q2 2017, returns sum of Sales for Q2 2016 — e.g. $187,225
Use Case Year-over-year comparisons in line charts; showing last year's sales
alongside current year.

DATEADD Time Intelligence

Syntax DATEADD(<dates>, <number_of_intervals>, <interval>)

Description Shifts a set of dates backward or forward by a given interval (DAY, MONTH,
QUARTER, YEAR).
Example Sales Prev Month = CALCULATE(SUM(Orders[Sales]),
DATEADD('Date'[Date], -1, MONTH))

Output Returns sales for the prior month. If current month = Nov 2017, returns Oct
2017 sales — e.g. $128,307
Use Case Month-over-month comparisons, rolling window analysis, and lag/lead
metrics.

DATESYTD Time Intelligence

Syntax DATESYTD(<dates> [, <year_end_date>])

Description Returns a table of dates from the beginning of the year through the last date

DAX Reference Guide · Superstore Dataset · Page 4

 in the current context.
Example Profit YTD = CALCULATE(SUM(Orders[Profit]),
DATESYTD('Date'[Date]))

Output Returns cumulative profit from Jan 1 through the selected date — e.g.
$47,138 through June 2017
Use Case Use inside CALCULATE for flexible YTD measures. Supports custom fiscal
year end (e.g. "3/31").

PREVIOUSMONTH Time Intelligence

Syntax PREVIOUSMONTH(<dates>)

Description Returns all dates from the previous month based on the current context.
Example Qty Prev Month = CALCULATE(SUM(Orders[Quantity]),
PREVIOUSMONTH('Date'[Date]))

Output Returns total quantity sold in the prior month — e.g. 1,783 units in Oct 2017
Use Case Trend lines showing current vs previous month sales, used in executive
summary dashboards.

DATESMTD Time Intelligence

Syntax DATESMTD(<dates>)

Description Returns a set of dates from the beginning of the month up to the last date in
the current context.
Example MTD Sales = CALCULATE(SUM(Orders[Sales]),
DATESMTD('Date'[Date]))

Output Returns sales accumulated within the current month — e.g. $48,906 for Dec
1–15 2017
Use Case Month-to-date sales cards updated daily, used in operational reporting
dashboards.

CALCULATE with DATESINPERIOD Time Intelligence

Syntax DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>,

<interval>)

Description Returns a table of dates for a rolling window period starting at a given date.
Example Rolling 90d Sales = CALCULATE(SUM(Orders[Sales]),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -90, DAY))

Output Returns total sales over the trailing 90 days from the latest selected date
Use Case Rolling average windows for trend smoothing, e.g. 30/60/90-day revenue or
profit analysis.

DAX Reference Guide · Superstore Dataset · Page 5

🔍 Filter Functions
Filter functions modify or inspect the filter context in which measures are evaluated. Mastering these is the key to
advanced DAX.

CALCULATE Filter — Core

Syntax CALCULATE(<expression> [, <filter1>] [, <filter2>] ...)

Description Evaluates an expression in a modified filter context. The single most

important DAX function.
Example West Region Sales = CALCULATE(SUM(Orders[Sales]),
Orders[Region] = "West")

Output Returns sales scoped only to the West region regardless of any visual-level
slicer — e.g. $725,458
Use Case Used to override, expand, or restrict filter context for virtually every advanced
measure.

FILTER Filter

Syntax FILTER(<table>, <filter_expression>)

Description Returns a filtered subset of a table. Used inside CALCULATE or as a table

argument.
Example High Value Orders = CALCULATE(COUNTROWS(Orders), FILTER(Orders,
Orders[Sales] > 1000))

Output Counts all orders where Sales exceeds $1,000 — e.g. 1,244 orders
Use Case Used for conditional aggregations, segment analysis (e.g. only profitable
orders), and parameterised filtering.

ALL Filter — Context Modifier

Syntax ALL(<table_or_column> [, <column1>] ...)

Description Removes all filters from a table or specified columns; used to compute totals
ignoring slicer context.
Example Sales % of Total = DIVIDE(SUM(Orders[Sales]),
CALCULATE(SUM(Orders[Sales]), ALL(Orders)))

Output For Technology category: returns 36.4% — the share of total company sales
Use Case Percentage-of-total columns, market share calculations, and benchmark
measures.

ALLEXCEPT Filter — Context Modifier

Syntax ALLEXCEPT(<table>, <column1> [, <column2>] ...)

Description Removes all filters from a table except those on the specified columns.

DAX Reference Guide · Superstore Dataset · Page 6

Example Sales % of Region = DIVIDE(SUM(Orders[Sales]),
CALCULATE(SUM(Orders[Sales]), ALLEXCEPT(Orders,
Orders[Region])))

Output For West / Technology: shows Technology's % of West region total,

preserving Region filter
Use Case Subtotal percentages; comparing sub-category performance within an intact
higher-level category.

VALUES Filter

Syntax VALUES(<table_or_column>)

Description Returns a single-column table of distinct values in the current filter context.
Example Selected Category = IF(HASONEVALUE(Orders[Category]),
VALUES(Orders[Category]), "Multiple")

Output If one category is selected → "Furniture"; if multiple → "Multiple"

Use Case Dynamic measure labels, detecting slicer selections, and conditional logic
based on selected values.

SELECTEDVALUE Filter

Syntax SELECTEDVALUE(<column> [, <alternate_result>])

Description Returns the value of a column if filtered to one unique value; otherwise
returns the alternate.
Example Region Label = SELECTEDVALUE(Orders[Region], "All Regions")

Output Returns "East" when East is selected; "All Regions" when no or multiple
regions are selected
Use Case Dynamic title generation in report visuals; conditional logic based on slicer
state.

KEEPFILTERS Filter — Context Modifier

Syntax KEEPFILTERS(<expression>)

Description Used inside CALCULATE to apply a filter as an intersection rather than a

replacement.
Example Safe Category Sales = CALCULATE(SUM(Orders[Sales]),
KEEPFILTERS(Orders[Category] = "Furniture"))

Output Returns Furniture sales only when the visual already shows Furniture;
otherwise blank.
Use Case Prevents a CALCULATE filter from overriding an existing slicer; useful in
cross-filter scenarios.

DAX Reference Guide · Superstore Dataset · Page 7

⚙️ Logical Functions
Logical functions evaluate conditions and control flow in calculated columns and measures.

IF Logical

Syntax IF(<logical_test>, <value_if_true> [, <value_if_false>])

Description Returns one value if a condition is TRUE and another if FALSE.

Example Profit Status = IF(Orders[Profit] > 0, "Profitable", "Loss")

Output Adds a column with "Profitable" or "Loss" for every order row
Use Case Segmenting orders, products, or customers into performance tiers for colour-
coded visuals.

SWITCH Logical

Syntax SWITCH(<expression>, <value1>, <result1> [, <value2>,

<result2>] ... [, <else>])

Description Evaluates an expression against a list of values and returns the matching
result. Cleaner than nested IFs.
Example Region Label = SWITCH(Orders[Region], "West","🌵 West",
"East","🗽 East", "Central","🌽 Central", "South","🌴 South",
"Unknown")

Output Returns a labelled emoji string for each region — readable in slicers and
tooltips
Use Case Mapping codes or abbreviations to full names, scoring tiers, and readable
category labels.

AND / OR Logical

Syntax AND(<logical1>, <logical2>) | OR(<logical1>, <logical2>)

Description Logical AND/OR for use in DAX formulas (alternative: && and || operators).
Example Top Performer = IF(AND(Orders[Sales] > 500, Orders[Profit] >
100), "Yes", "No")

Output Flags rows where both Sales > $500 AND Profit > $100 as 'Yes'
Use Case Building multi-condition flags for conditional formatting, filtered measures,
and segment tagging.

IFERROR Logical

Syntax IFERROR(<value>, <value_if_error>)

Description Returns a specified value if the expression evaluates to an error; otherwise

returns the expression value.
Example Safe Margin = IFERROR(DIVIDE(Orders[Profit], Orders[Sales]), 0)

DAX Reference Guide · Superstore Dataset · Page 8

 Output Returns 0 instead of a division-by-zero error when Sales = 0
Use Case Preventing error states in calculated columns or measures from breaking
visuals.

IN operator Logical

Syntax <value> IN {<list>}

Description Tests whether a value belongs to a list of values. Returns TRUE or FALSE.
Example Is Key Segment = Orders[Segment] IN {"Corporate", "Home
Office"}

Output Returns TRUE for Corporate and Home Office rows, FALSE for Consumer
Use Case Filtering to relevant sub-groups without long OR chains; useful in calculated
columns and CALCULATE filters.

🔤 Text Functions
Text functions manipulate string values in calculated columns and can aid in data cleaning and label generation.

CONCATENATE / & Text

Syntax CONCATENATE(<text1>, <text2>) or <text1> & <text2>

Description Joins two or more text strings into one. The & operator supports multiple
values directly.
Example Order Label = Orders[Order ID] & " - " & Orders[Customer Name]

Output Returns "CA-2017-152156 - Claire Gute" — a combined identifier for tooltips

Use Case Creating unique display keys, tooltip labels, and concatenated descriptions
for drill-through pages.

LEFT / RIGHT / MID Text

Syntax LEFT(<text>, <n>) | RIGHT(<text>, <n>) | MID(<text>, <start>,

<n>)

Description Extracts a specified number of characters from the left, right, or middle of a
string.
Example Order Year Code = LEFT(Orders[Order ID], 2) -- returns "CA"
prefix Year Extract = MID(Orders[Order ID], 4, 4) -- returns
"2017"

Output LEFT → "CA" | MID → "2017" from Order ID "CA-2017-152156"

Use Case Parsing encoded IDs, extracting region codes or year segments from
structured string keys.

DAX Reference Guide · Superstore Dataset · Page 9

UPPER / LOWER / PROPER Text

Syntax UPPER(<text>) | LOWER(<text>) | PROPER(<text>)

Description Converts text to all uppercase, all lowercase, or title case (first letter of each
word capitalised).
Example Display Name = PROPER(Orders[Customer Name])

Output Converts "CLAIRE GUTE" → "Claire Gute" for clean visual display
Use Case Standardising inconsistently cased data imported from multiple source
systems.

LEN Text

Syntax LEN(<text>)

Description Returns the number of characters in a text string including spaces.

Example Name Length = LEN(Orders[Customer Name])

Output Returns 11 for "Claire Gute"

Use Case Data quality checks — e.g. flagging unusually short or long product names or
IDs.

SEARCH / FIND Text

Syntax SEARCH(<find_text>, <within_text> [, <start_pos>] [,

<not_found_value>])

Description Returns the position of one string within another. SEARCH is case-
insensitive; FIND is case-sensitive.
Example Has Tech = IF(ISNUMBER(SEARCH("Tech", Orders[Category])),
"Yes", "No")

Output Returns "Yes" for rows where Category contains "Tech" (e.g. Technology)
Use Case Keyword detection in product names, flagging rows containing specific terms
for segment analysis.

SUBSTITUTE / REPLACE Text

Syntax SUBSTITUTE(<text>, <old_text>, <new_text>) REPLACE(<text>,

<start>, <num_chars>, <new_text>)

Description SUBSTITUTE replaces all occurrences of a string; REPLACE replaces

characters at a position.
Example Clean Sub-Cat = SUBSTITUTE(Orders[Sub-Category], "-", " ")

Output Converts "Binders-Staples" → "Binders Staples" for cleaner display

Use Case Fixing imported data, removing special characters, and reformatting category
labels.

DAX Reference Guide · Superstore Dataset · Page 10

 FORMAT Text

Syntax FORMAT(<value>, <format_string>)

Description Converts a value to text using a specified format. Useful for custom number
and date display.
Example Sales Display = FORMAT(SUM(Orders[Sales]), "$#,##0.00") Month
Name = FORMAT(Orders[Order Date], "MMM YYYY")

Output Sales → "$2,297,200.86" | Date → "Nov 2017"

Use Case Dynamic axis labels, formatted KPI cards with currency, and custom date
labels on charts.

🔢 Mathematical Functions
Mathematical functions perform numeric transformations on column values or expressions.

ROUND / ROUNDUP / ROUNDDOWN Mathematical

Syntax ROUND(<number>, <num_digits>) | ROUNDUP | ROUNDDOWN

Description Rounds a number to a specified number of decimal places.

Example Rounded Margin = ROUND(DIVIDE(SUM(Orders[Profit]),
SUM(Orders[Sales])) * 100, 2)

Output Returns 12.47 (rounded to 2 decimal places) instead of 12.4729…

Use Case Displaying clean percentage metrics on KPI cards and data tables.

DIVIDE Mathematical

Syntax DIVIDE(<numerator>, <denominator> [, <alternate_result>])

Description Safe division that returns an alternate value (default: BLANK()) when
denominator is 0.
Example Profit Margin % = DIVIDE(SUM(Orders[Profit]),
SUM(Orders[Sales]), 0)

Output Returns 0.1247 (12.47%) and avoids divide-by-zero errors when Sales = 0
Use Case The preferred division function in DAX — always use DIVIDE instead of the /
operator.

ABS Mathematical

Syntax ABS(<number>)

Description Returns the absolute (positive) value of a number.

Example Absolute Discount Impact = ABS(SUM(Orders[Profit]) -
SUM(Orders[Sales]))

DAX Reference Guide · Superstore Dataset · Page 11

 Output Returns a positive number representing the magnitude of the gap regardless
of sign
Use Case Variance analysis — showing absolute deviations without negative signs
confusing stakeholders.

INT / TRUNC Mathematical

Syntax INT(<number>) | TRUNC(<number> [, <num_digits>])

Description INT rounds down to the nearest integer; TRUNC truncates decimal digits
without rounding.
Example Days to Ship = INT(Orders[Ship Date] - Orders[Order Date])

Output Returns 3 for an order placed on Jan 5 and shipped Jan 8

Use Case Calculating shipping lead time in whole days; bucketing continuous values
into integer groups.

MOD Mathematical

Syntax MOD(<number>, <divisor>)

Description Returns the remainder after dividing a number by a divisor.

Example Order Batch = MOD(Orders[Quantity], 10)

Output Returns the leftover units when grouping by packs of 10 — e.g. 7 for Quantity
27
Use Case Determining remainder quantities for partial-case order analysis or batch
processing logic.

POWER / SQRT Mathematical

Syntax POWER(<number>, <power>) | SQRT(<number>)

Description POWER raises a number to a given exponent; SQRT returns the square root.
Example CAGR = POWER(DIVIDE([Sales End], [Sales Start]), DIVIDE(1, 4))
- 1

Output Returns 0.0842 (8.42% compound annual growth rate over 4 years)
Use Case Financial growth rate calculations, index numbers, and statistical
transformations.

Date & Time Functions

Date and Time functions extract components from dates or create date values, complementing the Time
Intelligence category.

TODAY / NOW Date & Time

DAX Reference Guide · Superstore Dataset · Page 12

Syntax TODAY() | NOW()

Description TODAY() returns the current date; NOW() returns the current date and time.
Example Days Since Last Order = TODAY() - MAX(Orders[Order Date])

Output Returns the number of days since the most recent order in the dataset
Use Case Creating real-time freshness indicators, days-open tickets, or elapsed-time
metrics.

DATE Date & Time

Syntax DATE(<year>, <month>, <day>)

Description Creates a date value from year, month, and day integers.
Example Q4 Start = DATE(2017, 10, 1)

Output Returns 01-Oct-2017 as a date value

Use Case Hardcoding date boundaries for conditional logic, e.g. flagging orders placed
in Q4.

YEAR / MONTH / DAY Date & Time

Syntax YEAR(<date>) | MONTH(<date>) | DAY(<date>)

Description Extracts the year, month number, or day number from a date column.
Example Order Year = YEAR(Orders[Order Date]) Order Month =
MONTH(Orders[Order Date])

Output Order Year → 2017 | Order Month → 11 (for November 2017)

Use Case Building date hierarchy columns, grouping data by fiscal year/month, and
time-based calculated columns.

WEEKDAY / WEEKNUM Date & Time

Syntax WEEKDAY(<date> [, <return_type>]) | WEEKNUM(<date>)

Description WEEKDAY returns a number (1–7) for day of week; WEEKNUM returns the
week number in the year.
Example Is Weekend = IF(WEEKDAY(Orders[Order Date], 2) >= 6, "Weekend",
"Weekday")

Output Returns "Weekend" for Saturday/Sunday orders, "Weekday" otherwise

Use Case Analysing order patterns by day of week; identifying whether sales peaks fall
on weekdays.

DATEDIFF Date & Time

Syntax DATEDIFF(<start_date>, <end_date>, <interval>)

Description Returns the number of intervals between two dates. Interval: SECOND,

DAX Reference Guide · Superstore Dataset · Page 13

 MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.
Example Shipping Days = DATEDIFF(Orders[Order Date], Orders[Ship Date],
DAY)

Output Returns 3 for an order shipped 3 days after placement

Use Case Calculating fulfilment speed by ship mode; SLA compliance and delivery
performance dashboards.

EOMONTH Date & Time

Syntax EOMONTH(<start_date>, <months>)

Description Returns the last day of a month offset by the specified number of months.
Example Month End = EOMONTH(Orders[Order Date], 0)

Output Returns 30-Nov-2017 for any order in November 2017

Use Case Used to create month-end date columns for period boundary logic in time
intelligence measures.

ℹ️ Information Functions
Information functions interrogate the data type or blank status of values, enabling robust error-handling and data
quality checks.

ISBLANK Information

Syntax ISBLANK(<value>)

Description Returns TRUE if a value is blank (NULL), FALSE otherwise.

Example Has Discount = IF(ISBLANK(Orders[Discount]), "No Discount",
"Discounted")

Output Labels rows without a discount value as 'No Discount'; others as 'Discounted'
Use Case Data quality audits, conditional logic when columns may have NULL values,
and filtering blank rows.

ISNUMBER / ISTEXT Information

Syntax ISNUMBER(<value>) | ISTEXT(<value>)

Description Returns TRUE if the value is numeric / text respectively.

Example Valid Sales = IF(ISNUMBER(Orders[Sales]), Orders[Sales], 0)

Output Returns the Sales value if it is numeric; otherwise returns 0 to prevent errors
Use Case Data validation in imported datasets where columns may have mixed types
due to ETL issues.

DAX Reference Guide · Superstore Dataset · Page 14

 HASONEVALUE Information

Syntax HASONEVALUE(<column>)

Description Returns TRUE when the column has exactly one distinct value in the current
filter context.
Example Dynamic Title = IF(HASONEVALUE(Orders[Category]), "Category: "
& SELECTEDVALUE(Orders[Category]), "All Categories")

Output When Furniture slicer active: "Category: Furniture" | No slicer: "All

Categories"
Use Case Generating dynamic report titles and measure labels that respond to slicer
selections.

CONTAINS Information

Syntax CONTAINS(<table>, <column1>, <value1> [, ...])

Description Returns TRUE if any row in the table has the specified column values.
Example Has West Orders = CONTAINS(Orders, Orders[Region], "West")

Output Returns TRUE if any order exists in the West region — used in conditional
card formatting
Use Case Checking data existence before computing a metric; used in dynamic
measure branching.

USERRELATIONSHIP Information

Syntax USERELATIONSHIP(<column1>, <column2>)

Description Activates an inactive relationship between two columns for the duration of a
CALCULATE expression.
Example Ship Date Sales = CALCULATE(SUM(Orders[Sales]),
USERELATIONSHIP(Orders[Ship Date], 'Date'[Date]))

Output Returns total sales evaluated by Ship Date rather than Order Date
Use Case Role-playing dimensions — using a single Date table for both Order Date and
Ship Date analyses.

🔗 Relationship Functions
Relationship functions navigate between related tables, allowing measures and calculated columns to access
fields from connected tables.

RELATED Relationship

Syntax RELATED(<column>)

Description Retrieves a related value from another table following the many-to-one
direction of a relationship.

DAX Reference Guide · Superstore Dataset · Page 15

 Example Product Category = RELATED(Products[Category])

Output Adds a Category column to the Orders table by looking up the related
Products table — e.g. "Furniture"
Use Case Denormalising related table attributes into fact tables for calculated column
creation.

RELATEDTABLE Relationship

Syntax RELATEDTABLE(<table>)

Description Returns a table filtered to rows related to the current row in a one-to-many
relationship.
Example Orders per Customer = COUNTROWS(RELATEDTABLE(Orders))

Output In the Customers table: returns the count of orders per customer — e.g. 6 for
Claire Gute
Use Case Computing customer-level metrics (total orders, average spend) from a
lookup/dimension table.

CROSSFILTER Relationship

Syntax CROSSFILTER(<column1>, <column2>, <direction>)

Description Overrides the cross-filter direction of a relationship inside CALCULATE.

Example Customer Count = CALCULATE(DISTINCTCOUNT(Orders[Customer ID]),
CROSSFILTER(Orders[Product ID], Products[Product ID], BOTH))

Output Enables bidirectional filtering for this calculation — returns customer count
filtered by product attributes
Use Case Enabling cross-filter scenarios in star schemas without permanently setting
BOTH-direction relationships.

📈 Statistical Functions
Statistical functions provide descriptive statistics beyond simple aggregations — useful for financial analysis,
forecasting, and performance benchmarking.

STDEV.P / STDEV.S Statistical

Syntax STDEV.P(<column>) | STDEV.S(<column>)

Description Returns the population (P) or sample (S) standard deviation of a numeric
column.
Example Sales Std Dev = STDEV.S(Orders[Sales])

Output Returns ~$454.14 — indicating high variability in order size around the $229
average
Use Case Identifying volatility in sales, pricing consistency analysis, and outlier

DAX Reference Guide · Superstore Dataset · Page 16

 detection.

VAR.P / VAR.S Statistical

Syntax VAR.P(<column>) | VAR.S(<column>)

Description Returns the variance of a numeric column (square of standard deviation).

Example Profit Variance = VAR.S(Orders[Profit])

Output Returns a large variance value indicating wide spread of profit across orders
Use Case Used in risk analysis dashboards to measure consistency of profitability
across product lines.

MEDIAN Statistical

Syntax MEDIAN(<column>)

Description Returns the median (middle value) of a numeric column.

Example Median Order Value = MEDIAN(Orders[Sales])

Output Returns ~$54.49 — significantly lower than the mean, indicating right-skewed
distribution
Use Case Provides a more representative central tendency than AVERAGE when large
outliers are present.

[Link] Statistical

Syntax [Link](<column>, <k>)

Description Returns the k-th percentile (inclusive) of a column, where k is between 0 and
1.
Example P90 Sales = [Link](Orders[Sales], 0.9)

Output Returns ~$492.40 — 90% of orders are below this value

Use Case Identifying top 10% customers by spend, setting thresholds for performance
tiers.

RANKX Statistical

Syntax RANKX(<table>, <expression> [, <value>] [, <order>] [, <ties>])

Description Returns the ranking of a value in a list for each row in a table.
Example Category Rank = RANKX(ALL(Orders[Category]),
SUM(Orders[Sales]))

Output Technology → 1 | Furniture → 2 | Office Supplies → 3

Use Case Leaderboard tables, top-N analysis, ranked bar charts, and highlighting
best/worst performers.

DAX Reference Guide · Superstore Dataset · Page 17

TOPN Statistical

Syntax TOPN(<n_value>, <table>, <orderBy_expression> [, <order>])

Description Returns the top N rows of a table based on an expression.

Example Top 5 Customers = SUMX(TOPN(5, VALUES(Orders[Customer Name]),
[Total Sales], DESC), [Total Sales])

Output Returns the combined sales of the top 5 customers by revenue

Use Case Building Top 10 product or customer analyses; creating summary KPIs for
high-value segments.

GEOMEAN Statistical

Syntax GEOMEAN(<column>)

Description Returns the geometric mean — ideal for rates, ratios, and growth
percentages.
Example Avg Growth Rate = GEOMEAN(SalesGrowthTable[Growth Rate])

Output Returns compound average growth rate (CAGR) across multiple periods —
more accurate than arithmetic mean for rates
Use Case Analysing average discount rates, margin percentages, or YoY growth rates
accurately.

DAX Reference Guide · Superstore Dataset · Page 18