What is the difference between a Calculated Column and a Measure?

This is the most important concept in DAX: Calculated Columns : Are computed during data refresh and stored in your model. They take up memory (RAM) and are calculated row-by-row. Use them for slicing and filtering (Dimensions). Measures : Are calculated on the fly, usually in response to a user clicking a visual. They don't take up space and are highly efficient. Use them for numerical totals (Metrics).

What is "Evaluation Context"?

DAX operates using two types of context that determine the result of a formula: Row Context: This exists when a formula iterates through a table row-by-row (like in a Calculated Column). Filter Context: This is the "environment" created by your report—filters, slicers, and the specific cells in a pivot table or chart.

Why is the CALCULATE function so important?

CALCULATE is the most powerful function in DAX. It is the only function that allows you to modify the filter context. For example, if you want to compare "Current Sales" against "Total Sales for the Whole Year," you use CALCULATE to tell DAX to ignore the current date filters and look at the entire year instead.

What is the difference between SUM and SUMX?

SUM : Is an aggregator. It looks at a single column and adds everything up. It cannot perform logic across different columns (like Price * Quantity). SUMX : Is an Iterator. It goes through a table row-by-row, performs a calculation (e.g., Price * Quantity ), and then sums the results of those individual rows. Generally, use SUM for simple totals and SUMX for row-level logic.

What are "Time Intelligence" functions?

DAX features a specific set of functions (like TOTALYTD , SAMEPERIODLASTYEAR , and DATEADD ) designed to make date-based calculations easy. These allow you to compare performance across different time frames without writing complex logic, provided you have a proper Date Table in your model.

Understanding DAX: A Glossary of Essential Terms

Clear Definitions of Key DAX Terms

Explore the DAX glossary below. Each term is listed individually for easy reference and future expansion with examples or best practices.

Categories

Aggregation & Math

Filter & Context

Time Intelligence

Logical & Conditional

Iterator Functions

Text & Formatting

Relationship & Lookup

Evaluation & Row Context

Table & Virtual Table Functions

Parent-Child & Hierarchies

1. Aggregation & Math Functions

Functions that perform totals, averages, ratios, and row-level calculations.

SUM

Adds all numeric values in a column. Commonly used for totals like revenue, cost, or quantity.

AVERAGE

Calculates the mean of values in a column.

DIVIDE

Performs division with built-in error handling for divide-by-zero scenarios. Ideal for ratios and percentages.

COUNTROWS

Returns the number of rows in a table. Often used to count filtered records or group sizes.

QUOTIENT

Returns the integer portion of a division. Useful for calculations involving whole units or buckets.
Read more about QUOTIENT

MOD

Returns the remainder after division. Used in cyclical logic or pattern detection.
Read more about MOD

2. Filter & Context

Functions that shape or override filter context — essential for CALCULATE logic.

FILTER

Returns a table subset based on a condition. Often nested inside CALCULATE for targeted logic.

ALL

Removes filters from a column or table. Used to calculate totals or benchmarks.

ALLEXCEPT

Removes all filters except those on specified columns. Useful for grouped totals or semi-granular comparisons.

VALUES

Returns the unique values from a column. Used in slicers, dynamic titles, or virtual tables.

REMOVEFILTERS

Explicitly clears filters from a column or table. Often used to reset context inside CALCULATE.

KEEPFILTERS

Preserves filters when used inside CALCULATE. Useful for layering logic without overriding visuals.

CALCULATE

The CALCULATE function in DAX evaluates an expression in a modified filter context. It is one of the most powerful functions in DAX, allowing you to apply filters dynamically and perform calculations under specific conditions.

3. Time Intelligence

Functions for comparing periods, calculating YTD/QTD/MTD, and shifting dates.

SAMEPERIODLASTYEAR

Returns the same period from the previous year. Used for YoY comparisons in KPIs and visuals.

Read more about SAMEPERIODLASTYEAR in Power BI

DATESYTD

Returns dates from the start of the year to the current date. Ideal for cumulative metrics.

DATEADD

Shifts dates by a specified interval (e.g., -1 month). Enables rolling comparisons and trend overlays.

TOTALYTD

Calculates year-to-date totals. Requires a marked date table for accuracy.

PARALLELPERIOD

Returns a parallel period offset by interval. Useful for comparing non-standard time frames.

4. Logical & Conditional

Functions that return values based on logic tests or branching conditions.

IF

Returns one value if a condition is true, another if false. Core to flags, commentary, and thresholds.

SWITCH

Returns values based on multiple conditions. Cleaner than nested IFs for scoring or categorisation.

AND

Returns TRUE if all conditions are met. Used in compound logic.

OR

Returns TRUE if any condition is met. Useful for flexible filters or commentary triggers.

NOT

Reverses a logical value. Used to exclude or invert conditions.

IFERROR

Returns an alternate value if an error occurs. Ideal for fallback logic in visuals.

5. Iterator Functions

Functions that evaluate expressions row by row across a table.

SUMX

Sums an expression across a table. Used for dynamic totals like revenue or margin.

AVERAGEX

Averages an expression across a table. Ideal for weighted averages.

MAXX

Returns the maximum value of an expression across a table. Used in performance metrics.

MINX

Returns the minimum value of an expression across a table. Useful for thresholds or alerts.

COUNTX

Counts values from an expression across a table. Used for filtered counts or flags.

RANKX

Ranks values based on an expression. Ideal for leader boards and performance visuals.

6. Text & Formatting

Functions that manipulate or format text — useful for dynamic titles and commentary.

CONCATENATE

Joins two text strings. Used for labels, commentary, or dynamic titles.

FORMAT

Converts values to formatted text. Ideal for currency, percentages, or branded visuals.

LEFT

Returns the leftmost characters from a text string. Used in parsing or categorisation.

RIGHT

Returns the rightmost characters from a text string. Useful for codes or suffixes.

MID

Returns characters from the middle of a text string. Used in structured text extraction.

UPPER / LOWER

Converts text to uppercase or lowercase. Useful for standardising labels or matching logic.

7. Relationship & Lookup

Functions that retrieve values across related tables or simulate joins.

Retrieves a value from a related table. Enables cross-table insights in visuals.

Read more about Power BI RELATED Function

RELATEDTABLE

Returns a related table based on relationships. Used in nested logic or virtual tables.

LOOKUPVALUE

Finds a value based on conditions. Simulates a VLOOKUP-style join.

TREATAS

Applies a table as a filter to another. Used in advanced filtering across models.

USERELATIONSHIP

Activates an inactive relationship. Essential for alternate joins or time intelligence.

8. Evaluation & Row Context

Functions that manage row context or enable nested logic in calculated columns.

EARLIER

Refers to a prior row context. Used in calculated columns for nested logic.

ISFILTERED

Checks if a column is filtered. Triggers dynamic commentary or formatting.

HASONEVALUE

Checks if a column has a single value. Used to control visuals or commentary logic.

SELECTEDVALUE

Returns the selected value or a default. Ideal for dynamic titles and commentary blocks.

ISINSCOPE

Checks if a column is in the current evaluation scope. Used in visuals and conditional logic.

9. Table & Virtual Table Functions

Functions that return or manipulate entire tables — often used inside CALCULATE.

SUMMARIZE

Groups data and returns aggregated results. Used for custom tables and overlays.

ADDCOLUMNS

Adds calculated columns to a table. Enables enriched visuals and commentary logic.

CROSSJOIN

Returns all combinations of two tables. Used in scenario overlays or simulations.

GENERATE

Combines two tables row by row. Ideal for audit trails or expanded logic.

SELECTCOLUMNS

Returns a table with selected columns. Used to reshape data for visuals or logic.

UNION

The UNION function in DAX returns a table that contains all rows from two or more tables, removing duplicate rows. It is useful for combining datasets with the same structure.
UNION in DAX

10. Parent-Child & Hierarchies

Functions for navigating hierarchies like org charts or product categories.

PATH

Returns a delimited path of IDs. Used to trace hierarchy levels.

PATHITEM

Returns a specific item from a path. Enables level-based logic or commentary.

PATHLENGTH

Returns the number of items in a path. Used to determine depth or seniority.

ISANCESTOR

Checks if one item is an ancestor of another. Used in hierarchy filtering or commentary.

Get Involved:
Suggest Terms & Request Clarification

Help Us Make the Glossary Even Better

We’re committed to keeping our glossary comprehensive and up to date, but we know there’s always room for improvement. If you’ve come across a term that’s missing or need further explanation on any topic, we’d love to hear from you.

Suggest a New Term

Have a technical phrase or concept you think should be added? Let us know! Your input helps us ensure the glossary remains useful to everyone in the community.

Request Clarification

If you find any definition unclear or would like more examples, feel free to request clarification. We aim to provide clear, practical insights for all users.

Share Your Feedback

Email your suggestions directly
Fill in our feedback form

Thank you for helping us build a better resource for everyone!

DAX Glossary– Frequently Asked Questions

What is the difference between a Calculated Column and a Measure?
This is the most important concept in DAX:
- Calculated Columns: Are computed during data refresh and stored in your model. They take up memory (RAM) and are calculated row-by-row. Use them for slicing and filtering (Dimensions).
- Measures: Are calculated on the fly, usually in response to a user clicking a visual. They don't take up space and are highly efficient. Use them for numerical totals (Metrics).
What is "Evaluation Context"?
DAX operates using two types of context that determine the result of a formula:
1. Row Context: This exists when a formula iterates through a table row-by-row (like in a Calculated Column).
2. Filter Context: This is the "environment" created by your report—filters, slicers, and the specific cells in a pivot table or chart.
Why is the CALCULATE function so important?
CALCULATE is the most powerful function in DAX. It is the only function that allows you to modify the filter context. For example, if you want to compare "Current Sales" against "Total Sales for the Whole Year," you use CALCULATE to tell DAX to ignore the current date filters and look at the entire year instead.
What is the difference between SUM and SUMX?
- SUM: Is an aggregator. It looks at a single column and adds everything up. It cannot perform logic across different columns (like Price * Quantity).
- SUMX: Is an Iterator. It goes through a table row-by-row, performs a calculation (e.g., Price * Quantity), and then sums the results of those individual rows. Generally, use SUM for simple totals and SUMX for row-level logic.
What are "Time Intelligence" functions?
DAX features a specific set of functions (like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD) designed to make date-based calculations easy. These allow you to compare performance across different time frames without writing complex logic, provided you have a proper Date Table in your model.

Other DAX Resources

You can read all of our DAX blog archives here

DAX Blog Archives – Select Distinct