Command Palette
Search for a command to run...
Comments
Join the discussionNo comments yet. Be the first to comment.
Search for a command to run...
No comments yet. Be the first to comment.
Data Analysis Expressions (DAX) is the formula language for the data modeling layer in Microsoft Power BI, Excel Power Pivot, and SQL Server Analysis Services (Tabular mode). DAX allows analysts to create custom calculations over their data in a relational model. It is similar in syntax to Excel formulas, but operates on tables and columns rather than individual cells. DAX formulas are used to create calculated columns, measures, and even entire tables within the data model. This enables dynamic analyses where results respond to user selections and filters (Context in DAX Formulas - Microsoft Support). In Power BI and Power Pivot, DAX is essential for building interactive reports and dashboards.
Key characteristics of DAX:
Tip: If you're familiar with Excel formulas, think of DAX as serving a similar purpose but for tables of data. Unlike Excel’s cell-by-cell computation, DAX computes over entire columns and tables at once, leveraging an in-memory engine for fast aggregation. Always ensure you have a well-designed data model (with lookup tables and relationships) to get the most out of DAX.
A Calculated Column is a new column added to a table in the data model whose values are defined by a DAX formula (expression). When you create a calculated column, the formula is evaluated for each row of the table, and the results are stored in the model. In other words, a calculated column uses a row context: it knows the “current row” of the table and computes a value based on that row’s data ( Row Context and Filter Context in DAX - SQLBI). Once computed, the values exist in memory just like any other column in the table.
How to create: In Power BI Desktop, use New Column and write an expression. For example, to add a profit margin column in a Sales table:
Profit = Sales[SalesAmount] - Sales[TotalCost]
This formula runs for every row of the Sales table, subtracting cost from sales amount for that row. In Power Pivot (Excel), you would insert a new column and provide a similar formula (without the “Sales[Column] =” part).
RELATED() (more on that in the Relationships section) because the row context doesn’t automatically traverse to lookup tables without help.Example – Creating a Calculated Column
Suppose we want a category for each product indicating if it’s a high-value item (price > $100). We have a Products table with a [Price] column. We create a new column:
HighValueFlag = IF(Products[Price] > 100, "High Value", "Standard")
This will mark each product as “High Value” or “Standard” based on its own price. The result is stored in the Products table as a new field. We can use this field in our visuals like any other column (for example, count of High Value products).
Note: The row context means each row’s calculation is independent. You cannot “peek” at other rows from a calculated column without an explicit aggregation or function. For instance, in a calculated column, you can’t directly ask for the “max price in the table” unless you wrap it in a function like
MAX(Products[Price]), which would ignore row context and give the same result for every row (the overall max). Generally, if you find yourself needing an aggregate in a calculated column, it might be a sign that you should use a measure instead.
When to use Calculated Columns:
Quick tip: It’s generally better to perform row-by-row calculations in the data source or Power Query (during data load) if possible. Use calculated columns sparingly for model-specific needs or conditional flags needed in the model. Overuse of calculated columns can lead to large models and memory pressure.
A Measure (sometimes called a calculated measure or calculation) is a DAX formula that is evaluated on the fly, in the context of the filter selections in your report (the filter context). Measures are not stored as a new column; instead, a measure is a formula saved in the model that computes a result at query time. Because of this, measures always reflect the current slicers, filters, and rows/columns of your report visualization (Measures vs Calculated Columns in DAX and Power BI). They are effectively re-calculated for each cell of a pivot table or visual.
How to create: In Power BI Desktop, clicking New Measure lets you write a formula that usually aggregates or computes over data. For example:
Total Sales = SUM( Sales[SalesAmount] )
This measure adds up the SalesAmount for whatever filters are applied. In an Excel pivot table or Power BI visual, if you put Total Sales and segment by Year, it will show the sum for each Year (each cell’s value differs based on the year filter).
Sales[SalesAmount] alone), DAX will throw an error because it doesn’t know which row’s value to take ( Row Context and Filter Context in DAX - SQLBI). Measures require aggregation or an expression that can work on potentially many rows. That’s why most measures use aggregator functions like SUM, AVERAGE, COUNT, etc., or more complex expressions with SUMX, CALCULATE, etc. Measures always produce a single value (scalar) for the context in which they are evaluated.Total Sales measure will yield the sum of SalesAmount only for European sales without you having to explicitly specify that in the formula. Similarly, placing fields on rows/columns of a pivot creates a filter context for each cell (the intersection of that row and column’s values) ( Row Context and Filter Context in DAX - SQLBI).Example measure calculations:
Total Quantity = SUM(Sales[Quantity])Avg Price = DIVIDE( [Total Sales], [Total Quantity] ) – Here we reused two measures to get an average unit price. Measures can reference other measures.Sales (High Value Customers) = CALCULATE( [Total Sales], Customers[HighValueFlag] = "High Value" ) – (Assuming there's a HighValueFlag column in Customers). This measure uses CALCULATE to modify filter context (more on CALCULATE later), so it only sums sales for customers flagged as High Value.Calculated Columns vs. Measures – Key Differences:
[Total Sales] aggregates many rows’ values into one number for whatever filters apply, but a calculated column has one result per row of the original table.(Measures vs Calculated Columns in DAX and Power BI) (Measures vs Calculated Columns in DAX and Power BI) summarizes these differences: a calculated column is computed for each row at data refresh (increasing model size), whereas a measure is computed at query time and is evaluated in the context of report filters and slicers (so it’s more dynamic).
Real-world example: If you wanted “Sales per Region”: You could add a calculated column on each sales transaction row for Region (via relationship) and then sum it, but that’s inefficient. Instead, create a measure that sums Sales and use Region from a related table to slice the data. The measure will automatically give the correct total per region in a chart or pivot, without needing a new stored column.
A Calculated Table is a table added to your model by writing a DAX formula that returns a table. Unlike calculated columns or measures, which live inside an existing table, a calculated table is a new table in the field list. You create them using the New Table feature in Power BI (or in the model diagram of Power Pivot) (Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn). Calculated tables are useful for intermediate results, staging tables, or supporting tables like a date/calendar table generated via DAX.
How to create: Write a DAX expression that returns a table (enclosed in curly braces for literal rows, or using functions that produce tables). For example, to combine two tables with identical structure (like appending rows):
CombinedTable = UNION( 'Northwest Employees', 'Southwest Employees' )
This creates a new table named CombinedTable with rows from both regional employee tables (Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn). The calculated table will appear in the Fields list, and you can create relationships to it or use its fields just like an imported table.
Usage examples:
Date Table: Many models create a Date table using DAX. For instance:
Calendar = CALENDAR( DATE(2020,1,1), DATE(2025,12,31) )
This produces a table with one date per day for the given range. You can then add calculated columns to this Calendar table for Year, Month, etc., or use built-in functions like YEAR() to create those columns. Mark this table as the official Date table in the model so time intelligence functions work properly.
Union or filtered tables: As in the previous example, combining two or more tables (UNION), or taking a subset of a table. For instance, you could create a table of Top 100 Customers by revenue:
TopCustomers = TOPN( 100, SUMMARIZE( Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount]) ), [TotalSales], DESC )
This DAX snippet (advanced) creates a table of 100 customer IDs with highest sales. This can be used for special reporting or further analysis.
(Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn) (Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn) — Calculated tables let you add new tables based on data already in your model, using DAX formulas to define their content. They are best used for intermediate calculations or data that you want as part of the model (as opposed to measures which are calculated on the fly).
Best practice: Use calculated tables for supporting structures like a calendar (date) table or a table of specific reference values that you cannot easily get from the data source. If your goal is purely to display a summary in a report, often a measure or visual-level aggregation is sufficient. Calculated tables shine when you need the table for further model logic (relationships, reusable subsets, etc.). Keep in mind they increase model size and refresh time, so ensure they’re truly needed (for instance, a small static table of dates or categories is fine, but creating a huge calculated table of millions of rows might be better done in the query or source).
Data models in Power BI/Power Pivot are built with relationships between tables (often primary key to foreign key, like a Dimensional model with dimension tables and fact tables). Understanding how DAX works with relationships is critical:
Using RELATED() in calculated columns: In a calculated column, you often have a row context on a fact table and want to fetch a value from a related lookup table. DAX won’t automatically pull that value just by referencing it (unlike a VLOOKUP in Excel, you must explicitly tell DAX to traverse the relationship). The function RELATED(Table[column]) is used in a row context to get the corresponding value from the one-side (lookup) table for the current row’s relationship. For example, in a Sales table (many side) that has a relationship to Product (one side), if we want the product category name on each sales row, we could create:
CategoryName = RELATED( Product[Category] )
This will go to the Product table, find the row related to the current Sales row, and return the Category. RELATED() works when there is a single related row (which is true in a one-to-many where you’re on the many side and pulling from the one side) ( Using RELATED and RELATEDTABLE in DAX - SQLBI) ( Using RELATED and RELATEDTABLE in DAX - SQLBI).
RELATEDTABLE() for the inverse: If you are on the one side (say Product table) and want to bring in information from the many side (Sales), you can use RELATEDTABLE(Sales) which returns a table of all sales rows related to the current product ( Using RELATED and RELATEDTABLE in DAX - SQLBI). In a calculated column scenario, RELATEDTABLE can be used inside an aggregator. For instance, in the Product table you might define:
TotalProductSales = SUMX( RELATEDTABLE(Sales), Sales[Amount] )
This will iterate over all Sales rows related to the current product (via the relationship) and sum the Amount. Essentially, RELATEDTABLE gives you the table of related rows, and then you can aggregate or count them. This is less common in measures (measures have an easier way – they naturally sum by product if you slice by product), but it’s good for calculated columns that need facts.
Active vs Inactive relationships: Power BI allows multiple relationships between the same two tables, but only one can be active at a time (the one that propagates filters by default). The others are inactive and do nothing unless explicitly invoked. For example, you might have an Order Date and a Ship Date both linking Sales to the Date table. One (say Order Date) is active. If you want to create a measure that uses the Ship Date, you use the function USERELATIONSHIP inside a measure to activate the inactive relationship for that calculation ( Using USERELATIONSHIP in DAX - SQLBI) ( Using USERELATIONSHIP in DAX - SQLBI). For example:
Shipped Sales =
CALCULATE( [Total Sales],
USERELATIONSHIP( Sales[ShipDate], 'Date'[Date] )
)
Here, within this measure, the relationship on Sales[ShipDate] to Date is used instead of the default Sales[OrderDate] -> Date. USERELATIONSHIP is only valid inside CALCULATE (or functions that accept filter modifiers) ( Using USERELATIONSHIP in DAX - SQLBI). Once the CALCULATE is done, the active relationship reverts back. This technique is crucial for multi-date-table models (role-playing dimensions like Date).
CROSSFILTER(table1[col], table2[col], direction) as a CALCULATE modifier to override the relationship’s filtering direction (useful for certain calculations where you temporarily need a different filter behavior). Another function, TREATAS, can apply the values of a table or column as filters on another table’s column – effectively creating a relationship on the fly for a specific measure.Real-world perspective on relationships: If your model is well-designed with correct relationships, writing DAX measures becomes much easier, since you typically do not have to explicitly join tables. The filter context does the heavy lifting. For example, if you want “Sales for Contoso brand in 2021”, and your model has Sales -> Product -> Brand and Sales -> Date relationships, simply filtering Brand = Contoso and Year = 2021 in the report or within a CALCULATE will yield the correct sales. No manual joining in the DAX formula is needed. Understanding this allows you to trust the model and focus on what you want to calculate (sum, average, etc.) rather than how to join tables.
Tip: Always ensure you have necessary relationships in place (and the correct active one for default behaviors). Use
USERELATIONSHIPin measures to utilize any secondary relationships (like multiple dates), and consider marking your Date table as a “Date Table” in Power BI which helps time intelligence and makes sure relationships on date behave as expected. Avoid modeling pitfalls like many-to-many relationships unless necessary; if you do have them, be mindful that filter context might flow in less obvious ways.
Understanding context is foundational to mastering DAX. There are two primary types of context in DAX: Row context and Filter context. The combination of these (at any point of execution) is sometimes called the evaluation context. Context determines what data a DAX expression is currently operating over.
Row Context: This is essentially “the current row” in a table that an expression is iterating over ( Row Context and Filter Context in DAX - SQLBI). You have a row context whenever a formula is being evaluated for each row of a table. As discussed, calculated columns inherently have a row context (each row is calculated separately). Also, any iterator function (like SUMX, FILTER, AVERAGEX, etc.) creates a row context as it loops through a table ( Row Context and Filter Context in DAX - SQLBI). Within a row context, you can directly reference columns of that table, and it will understand you mean “the value of this column in the current row.” For example, in a SUMX(Sales, Sales[Quantity] * Sales[Price]), the expression Sales[Quantity] * Sales[Price] is evaluated in a row context for each row of Sales, so it multiplies quantity and price of that specific row.
If a row context is present, you must use functions like SUMX/AVERAGEX etc. to aggregate, or use CALCULATE to introduce a filter context (more on that soon) if you want to do operations across rows. Without a row context, a naked column reference is ambiguous and errors out (e.g., a measure cannot just say Sales[Amount] by itself) ( Row Context and Filter Context in DAX - SQLBI).
Another key point: Row context by itself does not do filtering on other tables automatically. In a calculated column, if you need a value from a related table, you still need RELATED() as discussed. Row context can propagate through relationships when using certain functions (like RELATEDTABLE which uses the current row’s context to fetch related rows ( Row Context and Filter Context in DAX - SQLBI)), but generally just having a row context on one table doesn’t filter other tables unless you explicitly tell DAX to do so.
Filter Context: This is the set of filters applied to the data model before evaluating a DAX expression ( Row Context and Filter Context in DAX - SQLBI). Filter context comes from a few places: the user’s selections in the report (slicers, filters, the current row/column of a pivot or visual), and filters applied within DAX formulas (like filter arguments in CALCULATE, or filters from measures in filter visuals). Filter context can be thought of as “a subspace of the data” that’s currently active. For instance, if you put Year=2022 and Region=Europe as page filters in Power BI, those constitute a filter context that will affect all measures (only 2022, Europe data is visible to them). If you add a visual with Product Category on rows, each category value in a row has an additional filter (Category = that value) for that specific evaluation.
In practical terms, when a measure is evaluated, first any relevant filters from the report are applied to the tables (this is like a WHERE clause limiting rows). Then the measure’s formula runs against that filtered data. If the measure is [Total Sales] and the filter context is Year=2022 and Region=Europe, it will sum only the sales that meet those criteria.
Filter context can also be modified within DAX (the primary function to do this is CALCULATE, which we’ll cover next). The filter context is additive by default – multiple filters combine (they function like an AND of conditions). It’s possible to have no filter context (meaning all data is considered), such as a measure in a card with no filters applied, or you can explicitly clear filters using functions like ALL.
In summary: Row context = current row (for iterative calculations), Filter context = current filters (for aggregation). A single DAX calculation can have both contexts at play: e.g., in a SUMX, you have a row context iterating the table, and also a filter context coming from outside (like slicers on other fields). The row context applies to that inner expression, while the outer filter context restricts which rows of the table are iterated.
( Row Context and Filter Context in DAX - SQLBI) defines filter context as “the set of filters applied to the evaluation of a DAX expression.” Any pivot table cell or visual corresponds to a filter context (even if it's "all data", that's essentially an empty filter context meaning no filters). And ( Row Context and Filter Context in DAX - SQLBI) explains that a row context exists when iterating over each row in a calculated column or with an X iterator function.
Example to illustrate contexts:
Imagine a PivotTable showing Total Sales by Year and Product Category. When computing the measure for Year=2021 and Category=“Electronics”, the filter context is Year=2021 AND Category=Electronics (and any other global filters applied). The measure [Total Sales] = SUM(Sales[Amount]) will then sum only those sales that match those filters. There is no row-by-row loop in the measure; the Vertipaq engine (Power BI’s data engine) efficiently scans the pre-filtered data. Now, if we had a measure like Average Sale per Order = AVERAGEX( Sales, Sales[Amount] ), here AVERAGEX creates a row context iterating each Sale (each row in Sales table) within the current filter context. If Year=2021 and Category=Electronics filters are on, AVERAGEX will iterate only Sales rows for 2021 Electronics (filter context first), then within that, row context for each sale to pick Sales[Amount], and then average those. So filter context and row context worked together in that calculation.
Evaluation context is a term for the combined context that applies when an expression is evaluated – it includes any existing filter context and any row context(s) that might be in play. One of the more advanced concepts is context transition, which is how DAX converts a row context into a filter context (so that a measure or aggregation can be evaluated per row).
Context Transition: This typically occurs when you use the CALCULATE (or CALCULATETABLE) function. When CALCULATE is called from within a row context, it will take the values of the current row’s columns and treat them as filter context for the expression inside CALCULATE (Evaluation Contexts in DAX - Context Transition). In effect, it transitions the existing row context into an equivalent set of filters. This is what allows measures to work when called in a row context. For instance, if you write a calculated column like = CALCULATE( [Total Sales] ) in a Sales table, here [Total Sales] is a measure (which normally works on filter context). By wrapping it in CALCULATE, DAX will transition the row context (the current Sales row’s values for all relevant columns, such as Product, Date, etc.) into filters, then evaluate [Total Sales] under those filters – effectively giving you the sales of just that one row (which is actually just SalesAmount itself in this trivial example). This might seem pointless in a simple case, but it’s how a measure can be evaluated per row of another table or in an iterator. In fact, calling a measure within an iterator like SUMX automatically triggers context transition even without explicitly writing CALCULATE (CALCULATE function (DAX) - DAX | Microsoft Learn) – DAX sees a measure needing filter context and converts the current row context to filters for that measure.
Another example: Suppose you have a calculated table of customers and you want a column "Total Sales to Customer" in that table. You might do: Total Sales to Customer = CALCULATE( [Total Sales], Sales[CustomerID] = Customers[CustomerID] ). Here for each customer row, CALCULATE takes that customer’s ID and filters the Sales table to that ID, then [Total Sales] sums only those. This works because of context transition – effectively CALCULATE says "current row’s CustomerID -> apply it as a filter on Sales[CustomerID]". If you omitted CALCULATE and just tried [Total Sales] in that row context, it wouldn’t know the row context of Customers unless transitioned.
In summary, context transition allows row context to be converted to filter context, usually via CALCULATE (Evaluation Contexts in DAX - Context Transition). This is essential for many advanced calculations.
CALCULATE(Expression, filters...) in general, it creates a new filter context for evaluating Expression: starting from the existing one, then applying the filters you give. If CALCULATE is invoked where there is a current row context (like inside an iterator or a calculated column), that row context becomes part of the new filter context (context transition) (CALCULATE function (DAX) - DAX | Microsoft Learn). We will discuss CALCULATE more in the next section, but it’s important to highlight its role in context transition here. Also, a measure placed in a visual cell inherently has no row context, only filter context. If that measure was defined using row-based logic, it's likely using CALCULATE internally to simulate that.One way to think about evaluation context: Filter context restricts which rows are visible to the calculation, while row context dictates the current row from those rows when needed. When you call an aggregation like SUM, it looks at all rows visible in the filter context (ignoring row context unless context transition happened). When you are in an iterator, the iterator moves row by row (that’s the row context), but inside it you can still call CALCULATE or measures to leverage filter context logic.
To reinforce context transition with a straightforward statement: “When used inside a filter context, CALCULATE performs context transition, transforming the current row context into a filter context.” (Evaluation Contexts in DAX - Context Transition) This is often on exam tests and DAX theory — it means if you are in a row context and want to do something like a measure or any calculation that needs a filter context, wrapping it in CALCULATE will turn your row’s values into filters so the calc can happen.
Advanced note: Most DAX measures you write will implicitly handle context correctly without you needing to think about context transition. It mainly comes into play when writing calculated columns that use measures, or when writing measures that iterate (like using X functions or manual filters). A common pitfall for newcomers is thinking a measure can be used directly in a calculated column and yield row-specific results – it won’t, unless you wrap it in CALCULATE. If you ever see the same value repeated on every row of a calc column when using a measure, it’s likely because context transition was not applied and the measure was evaluated in the overall filter context (probably empty) instead of per row.
CALCULATE is the most powerful (and arguably most important) function in DAX. It allows you to modify the filter context under which an expression is evaluated. In other words, CALCULATE enables you to add or override filters for a calculation. This is how you perform tasks like “Sales for 2021 only” or “Number of customers in US, regardless of any country filter on the report” inside your DAX measures.
CALCULATE( <Expression>, <Filter1>, <Filter2>, ... ).<Expression> is typically a measure or an aggregation you want to compute. The filters can be given as Boolean filter expressions (like Table[Column] = value), table expressions (often using the FILTER() function or all/values functions), or special filter modifier functions (like ALL(), REMOVEFILTERS(), USERELATIONSHIP() etc.). Each filter argument modifies the context separately: they either add a new filter or override an existing one on the specified column/table.CALCULATE([Total Sales], Region[Country] = "USA"), the result inside that CALCULATE will act as if Region = USA (the filter on Europe is replaced for that measure). This replacement behavior is often what you want for explicit overrides. If instead you want to add a filter without removing existing ones on the same column, there’s a function KEEPFILTERS we can use (more on that in advanced tips).Basic usage examples:
Sales 2021 = CALCULATE( [Total Sales], 'Date'[CalendarYear] = 2021 ). This measure will give the total sales for the year 2021, no matter what year is filtered in the report (it overrides year filter to 2021). You can list multiple filters: e.g. CALCULATE( [Total Sales], 'Date'[CalendarYear]=2021, Product[Category]="Electronics" ) to apply both Year and Category filters at once (CALCULATE function (DAX) - DAX | Microsoft Learn).All Sales = CALCULATE( [Total Sales], REMOVEFILTERS( Product[Category] ) ). This would calculate total sales ignoring any filter on Product Category (i.e., returning total across all categories even if a specific category is in context). Similarly, ALL(Table) can be used to ignore filters on a whole table or column. (ALL used inside CALCULATE is considered a filter modifier, telling CALCULATE to clear those filters rather than add a new one).SalesAmount_Check = CALCULATE( [Total Sales] ) in the Sales table would for each row filter Sales to that row and compute [Total Sales], effectively just returning that row’s SalesAmount (this is a trivial example of context transition).FILTER function: FILTER is a DAX function that returns a table which is a subset of another table, filtering rows by a condition. Its syntax: FILTER( <Table>, <Condition> ). The condition is evaluated for each row of the table, and any row that returns TRUE is kept. FILTER is an iterator (it creates a row context over the table you provide and evaluates the condition for each row) (Explanation of Context in Calculating Cumulative Values). This means if the condition itself involves aggregates or references to other tables, it will still evaluate row by row (often you might see conditions like FILTER( Sales, Sales[Amount] > 1000 && Sales[Amount] < [Some Threshold] ) etc., where it filters on Sales rows meeting certain criteria).
By itself, FILTER is most often used as an argument to other functions that take a table. Common use cases:
CALCULATE( [Total Sales], FILTER( Sales, Sales[Amount] > 1000 ) ). Here, instead of a simple Sales[Amount] > 1000 filter (which is actually not allowed directly in CALCULATE because it’s not a simple equality), we use FILTER to apply a more complex condition. This will filter the Sales table to only rows where amount > 1000, then CALCULATE will evaluate [Total Sales] (which sums SalesAmount) under that modified context (so effectively “Sales of transactions > 1000”).COUNTROWS( FILTER( Customers, Customers[HasEmail] = TRUE ) ) which would count how many customers have an email. (This could also be done with a CALCULATE: CALCULATE( COUNTROWS(Customers), Customers[HasEmail] = TRUE ) – in fact, CALCULATE with simple conditions is often simpler.) Remember that FILTER(Table, condition) returns the filtered table, but doesn’t on its own do anything to existing filter context unless wrapped in CALCULATE or used in an iterator. So you often see it paired with functions like SUMX, COUNTX, CALCULATE, etc.
Column = Value) suffices in CALCULATE, use that rather than FILTER, because CALCULATE can set simple filters efficiently. FILTER should be used for more complex logic (such as multiple conditions, ranges, or involving measures/aggregates). For example, to filter on a measure or an aggregate, you must use FILTER (e.g., “filter products to those with sales > X” – you can’t put [Total Sales] > X directly as a CALCULATE filter without FILTER). But if you just need Product[Color] = "Red", you can pass that directly to CALCULATE. Under the hood, direct CALCULATE filters and FILTER(...) achieve the same outcome (modifying filter context), but direct filters are simpler and often faster.Example – Using CALCULATE and FILTER:
Let’s say we want a measure for High Value Sales: total sales where the transaction amount was over 1000. We have a measure [Total Sales]. We can do:
High Value Sales =
CALCULATE( [Total Sales],
FILTER( Sales, Sales[SalesAmount] > 1000 )
)
Here, FILTER(Sales, Sales[SalesAmount] > 1000) produces a table of only the sales transactions over 1000. CALCULATE then applies that as the filter context for [Total Sales]. The result is the sum of SalesAmount for those high-value transactions. We could also achieve this with an iterator: High Value Sales = SUMX( FILTER(Sales, Sales[SalesAmount] > 1000), Sales[SalesAmount] ) – which in effect does the same thing (iterates and sums). Using CALCULATE with an existing [Total Sales] measure is a bit cleaner.
Another example: Percentage of total often uses CALCULATE. Imagine [Total Sales] as current context sales, and you want a measure for “Percentage of All Sales that this context represents”. You could write:
% of All Sales =
DIVIDE( [Total Sales],
CALCULATE( [Total Sales], REMOVEFILTERS() )
)
The CALCULATE( [Total Sales], REMOVEFILTERS() ) part gives the total sales with all filters removed (i.e., the denominator is total sales for all data). This uses the filter modifier REMOVEFILTERS() to clear filters (CALCULATE function (DAX) - DAX | Microsoft Learn). The DIVIDE then gives the fraction. This pattern is common in DAX for percent of total, percent of parent, etc.
Think of CALCULATE as: "Evaluate this expression, but under these filter conditions (possibly in place of whatever external filters there were)." It’s how you explicitly control context inside a measure. Meanwhile, think of FILTER (function) as: "From this table, give me only the rows that meet this test." They often work together but serve different purposes.
Advanced filter modifiers: Within CALCULATE, you can use special functions:
ALL(Table/Column): ignores filters on that table/column (CALCULATE function (DAX) - DAX | Microsoft Learn). (Use ALL or REMOVEFILTERS to get overall totals or to override specific filters.)ALLEXCEPT(Table, Column1, Column2, ...): clears all filters on a table except the ones explicitly listed.KEEPFILTERS(FilterExpression): changes how filter application works by intersecting with existing filters instead of overwriting (DAX Best Practices | MAQ Software Insights). For example, CALCULATE( [Measure], KEEPFILTERS( Table[Column] = "Value" ) ) will keep any existing filters on Table[Column] and require that "Value" also be true (i.e., an intersection), whereas normally CALCULATE would replace any filter on that column with "Value". Use case: maybe a measure that refines an existing report filter rather than replacing it.USERELATIONSHIP(col1, col2): as mentioned, activates an inactive relationship for the calculation ( Using USERELATIONSHIP in DAX - SQLBI).CROSSFILTER(col1, col2, direction): temporarily change relationship filter direction (or disable it) for the calc.ALLSELECTED, ALLNOBLANKROW, but those are more specialized.(CALCULATE function (DAX) - DAX | Microsoft Learn) (Microsoft docs) underscores how CALCULATE modifies filter context by adding or overriding filters provided in its arguments. And (Explanation of Context in Calculating Cumulative Values) reminds us that FILTER returns a table of rows that meet a condition (and acts as an iterator).
Important: CALCULATE only works in measures or calculated columns, not in row-level security or DirectQuery mode in certain cases (with exceptions). But in general use, you’ll be writing CALCULATE a lot in measures. Also note, you cannot nest CALCULATE inside another CALCULATE’s filter (there’s a restriction on that), and you cannot use a measure as a direct filter argument (you must use a measure in a filter through something like FILTER or a comparison as part of a boolean expression).
DAX has a variety of aggregation functions (like SUM, MIN, MAX, COUNT, AVERAGE) and their iterator counterparts (SUMX, MINX, MAXX, COUNTX, AVERAGEX, etc.). Understanding the difference between these is key to writing correct calculations:
SUM(column), AVERAGE(column), MIN(column), MAX(column), COUNT(column) operate over a column in the current filter context. They take a column reference and compute the aggregate of all values currently visible in that column. For example, SUM(Sales[Amount]) adds all Sales Amount values in whatever context (filters) is active. These functions do not have an inherent row context; they implicitly consider all rows allowed by the filter context. They are highly optimized to work on the column data.Iterator functions (X functions): These include SUMX(table, expression), AVERAGEX(table, expression), MINX, MAXX, COUNTX, RANKX, etc. These functions iterate over a specified table, evaluating the given expression for each row, then aggregate the results. During their execution, a row context is created for the table they iterate ( Row Context and Filter Context in DAX - SQLBI). For instance, SUMX(Sales, Sales[Quantity] * Sales[Price]) will go row by row in the Sales table, compute Quantity * Price for each row, and then sum up those computed values. If you tried to achieve the same with simple SUM, you couldn’t directly, because you’d need to multiply per row first (which SUM can’t do by itself).
Another example: AVERAGEX( Dates, [Daily Sales] ) (imagine [Daily Sales] is a measure that gives sales on a single date context). This would iterate each date in the Dates table (perhaps filtered to a year), retrieve [Daily Sales] for that date (context transition happens for the measure inside, effectively), and then average those values.
Why use X iterators? They let you perform calculations per row that are more complex than just the raw column value, and then aggregate. Common scenarios:
CONCATENATEX which joins strings from rows).SUM(Sales[Amount]) is preferred to SUMX(Sales, Sales[Amount]) – they return the same result, but the latter introduces unnecessary row-by-row iteration. In fact, SUM(column) is essentially a shorthand for SUMX(table, column) where the engine can optimize it directly (SUM, SUMX – DAX Guide - SQLBI). As a rule: use the X version only when you need to do something per row that a simple function can’t handle.Examples:
Total Sales = SUM(Sales[Amount]) adds the Amount column directly (fast).Total Sales (SUMX) = SUMX( Sales, Sales[Amount] ) would theoretically do the same thing but less efficiently.Total Revenue = SUMX( Sales, Sales[Quantity] * Sales[UnitPrice] ). Here we multiply quantity and unit price per row (to get revenue per sale), then sum it up. There is no single column that directly has revenue, so SUMX is needed to evaluate the expression for each row.AVERAGEX( Products, Products[Margin] ) if [Margin] itself is a calculated column or expression. But if you wanted the overall gross margin (total profit / total sales), you’d better do it as a measure ratio (which might be different from averaging margins of each product). Distinguishing when to average an aggregate vs aggregate an average is important. AVERAGEX is row-by-row, then average; whereas AVERAGE just takes a column of already stored values.COUNTX(table, expression) will count the number of rows where the expression is not blank. A typical use might be something like counting non-empty results of an expression.Rank Sales by Product = RANKX( ALL(Product), [Total Sales], , DESC ) will rank each product’s total sales against all products. RANKX iterates over a table (here ALL(Product) which is the table of all products ignoring filters) and compares the [Total Sales] for each product to determine rank.(SUM, SUMX – DAX Guide - SQLBI) succinctly says: SUM adds all numbers in a column; SUMX returns the sum of an expression evaluated for each row of a table. That applies to other aggregates too (e.g. AVERAGE vs AVERAGEX, etc.).
SUMX(Sales, ...) and the report or outer CALCULATE has Year=2020 filter active, then that Sales table passed into SUMX is already filtered to 2020 sales. The iterator then goes through just those. You can also embed CALCULATE inside an iterator’s expression if needed to override context per row (advanced use case).Total Revenue = Sales[Quantity] * Sales[UnitPrice] as a measure will error out – because there’s no row context in a measure by default. The correct approach is SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]). Conversely, sometimes newcomers use SUMX when a SUM would do; e.g., SUMX(Sales, Sales[Amount]) – this works but is unnecessarily verbose. Overuse of iterators can hurt performance if the table is large, because it forces a row-by-row evaluation in the formula engine rather than a swift columnar aggregation.Table functions: In addition to X iterators, DAX has other functions that return tables (e.g., FILTER, ALL, VALUES, SUMMARIZE, ADDCOLUMNS, etc.). These aren't aggregators themselves but are used to shape data for either iterators or for producing calculated tables. For instance, VALUES(Column) returns a one-column table of distinct values (and in a measure context is often used to get a single value of a filter if present). ADDCOLUMNS(Table, "NewCol", Expression) can add a calculated column on the fly to a table (useful within measures to create a temporary table with extra data).
For most everyday use, focus on choosing between non-X and X correctly:
Quick Reference – Common Aggregators vs Iterators:
- SUM vs SUMX:
SUM(Column)adds up column values;SUMX(Table, Expr)evaluates Expr for each row and sums. (SUM, SUMX – DAX Guide - SQLBI)
- AVERAGE vs AVERAGEX:
AVERAGE(Column)vsAVERAGEX(Table, Expr)– similar pattern. Use AVERAGEX if the thing you want to average isn’t a base column.
- COUNT/COUNTROWS vs COUNTX:
COUNTROWS(Table)counts rows (optionally filtered via CALCULATE).COUNTX(Table, Expr)counts non-blank results of Expr per row. Often COUNTROWS combined with FILTER is sufficient for conditional counts (e.g.,CALCULATE(COUNTROWS(Sales), Sales[Amount] > 1000)to count transactions >1000).
- MIN/MAX vs MINX/MAXX: min or max of a column vs of an expression per row. For example,
MAXX( Dates, [Daily Sales] )might give the maximum daily sales value in the current context.
- DISTINCTCOUNT (counts distinct values in a column) doesn’t have an X version because the operation is inherently on a column set, but if you needed distinct count of an expression, you might do something like
COUNTROWS( DISTINCT( GENERATE( table, ... ) ) )which is more advanced. Typically stick to provided functions.
One of DAX’s strengths is built-in time intelligence – functions that make calculating time-based metrics easier. Time intelligence functions allow you to manipulate dates to get calculations like year-to-date, quarter-to-date, same period last year, year-over-year growth, moving averages, etc., without manually writing complex filter logic every time.
Prerequisite: Date Table. In order to use most time intelligence functions properly, you should have a dedicated Date table in your model (a table that contains all dates over the period of interest, with contiguous date ranges, with no missing dates). Mark this table as the "Date Table" in Power BI. The Date table should have a relationship to your fact table (e.g., Sales) on the date field. The functions assume this setup. If you don’t have a proper date table, functions like TOTALYTD or SAMEPERIODLASTYEAR might not work correctly. (Time intelligence functions (DAX) - DAX | Microsoft Learn) emphasizes marking a date table before using these functions.
Year-to-Date (YTD), Quarter-to-Date, Month-to-Date:
DAX provides functions like TOTALYTD( <Measure>, <DatesColumn> [, <FiscalYearEndDate>] ), TOTALQTD, TOTALMTD to accumulate a measure from the start of the year/quarter/month up to the current context date. For example:
Sales YTD = TOTALYTD( [Total Sales], 'Date'[Date] )
If you put this measure in a visual with Month, it will show running total of sales from Jan 1 to that month’s end for each month (assuming 'Date'[Date] is a continuous date column in your Date table). There are equivalent TOTALQTD, TOTALMTD for quarter and month. These are convenience wrappers around a combination of CALCULATE and filter logic on the date. They automatically detect year boundaries (or you can specify a fiscal year end).
Same Period Last Year / Previous Periods:
SAMEPERIODLASTYEAR(<dates>): returns the set of dates exactly one year before the dates in the current filter context. Often used like:
Sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Date'[Date] ) )
This gives the sales for the same period last year corresponding to the current filter context period. For example, if the current context is March 2025, it filters to March 2024’s dates and returns that sales.
PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH which give the entire previous period (regardless of alignment with current). For instance, if you’re filtering a specific month, PREVIOUSMONTH would give the entire immediately preceding month.DATEADD(<dates>, -1, YEAR): an alternative to SAMEPERIODLASTYEAR; DATEADD is more flexible since you can shift by any number of intervals (e.g., -1 year, -3 months, etc.). It returns a table of dates shifted by the interval. E.g., CALCULATE( [Total Sales], DATEADD('Date'[Date], -1, YEAR) ) is effectively same as above.Period-to-date vs period-over-period: YTD/QTD/MTD are cumulative from period start. For year-over-year or quarter-over-quarter comparisons, you typically use SAMEPERIODLASTYEAR or DATEADD to get a comparable period in the past, then maybe compute differences or percentages. Example:
Sales YoY % =
DIVIDE( [Total Sales] - CALCULATE( [Total Sales], SAMEPERIODLASTYEAR('Date'[Date]) ),
CALCULATE( [Total Sales], SAMEPERIODLASTYEAR('Date'[Date]) )
)
This measure calculates year-over-year growth percentage. It takes current sales minus last year’s sales, divided by last year’s sales.
FIRSTDATE, LASTDATE, which give the first or last date in the current filter context. And specialized ones like CLOSINGBALANCEMONTH(<expr>, <dates>), OPENINGBALANCEYEAR(<expr>, <dates>) which evaluate an expression at the end or start of a period. These are used for things like inventory or account balances where you need the value at a period boundary.TOTALYTD essentially does CALCULATE( <measure>, DATESYTD(<dates>) ) where DATESYTD returns all dates from the start of the year to the max date in the current context. Similarly SAMEPERIODLASTYEAR is essentially shifting that set by -1 year. If your Date table has gaps or multiple entries per day, results may be off.(Time intelligence functions (DAX) - DAX | Microsoft Learn) (Microsoft documentation) states: DAX includes time-intelligence functions that enable you to manipulate data using time periods (days, months, quarters, years) and build and compare calculations over those periods. It also reminds to mark a table as Date Table.
Example – Year-over-Year Sales:
Imagine you want a column chart showing sales this year vs last year by month. You’d have [Total Sales] and [Sales LY] as measures. We defined Sales LY above. You place Month on X-axis, and both measures as values. Thanks to the DAX time functions, for each month context, [Sales LY] automatically grabs the equivalent month last year’s data. This simplifies writing such comparisons.
Example – Year-to-Date Total:
If you show a cumulative line chart of sales across months, [Sales YTD] will give a running total. The measure we gave Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]) will accumulate from the start of each year. The function knows to reset at year boundaries (if using calendar year or the fiscal year you provide). Under the hood, TOTALYTD is doing something akin to:
Sales YTD =
CALCULATE( [Total Sales],
DATESYTD( 'Date'[Date] )
)
where DATESYTD returns all dates from Jan 1 of the current year up to the current date context.
Rolling averages or moving sums: There isn’t a single built-in for moving average (like 30-day rolling), but you can combine functions. For example, to get last 30 days sales:
Last30DaysSales =
CALCULATE( [Total Sales],
DATESINPERIOD( 'Date'[Date], MAX('Date'[Date]), -30, DAY )
)
Here DATESINPERIOD takes the max date in current context (say you’re at March 31, 2025 in a visual, max date is that) and goes back 30 days to produce that set of dates, and CALCULATE sums over that set. This would let you then do a moving average by dividing by 30 or adjusting context in a measure.
Time intelligence functions rely on having that full range of dates. They can handle fiscal years (with year_end parameter in TOTALYTD, etc.), and can work with quarters, months, weeks (though week-based calculations might require more custom logic or a week number in the date table).
Tip: Always use a dedicated Date table and relate it to fact tables. Use the built-in functions like TOTALYTD, SAMEPERIODLASTYEAR, etc., to save time – they implement common patterns. If you need something custom (like “sales in the same month of the previous year but align by fiscal week” or “trailing 12 months”), you might have to use combinations of
DATEADDorDATESINPERIOD. The DAX Patterns website and Microsoft docs have recipes for many of these scenarios (Week-Based Time Intelligence in DAX - SQLBI). Start with simple ones and be sure to test the results to ensure they match expected values.
DAX introduced the VAR and RETURN syntax to allow variables within expressions. Using variables can greatly improve the clarity, performance, and debuggability of your DAX formulas (Variables in DAX - SQLBI). A variable lets you compute a sub-expression once, name it, and reuse it multiple times within the same measure (or calculated column). Variables do not persist beyond the single evaluation of that formula; they are not like Excel named ranges that stick around globally – they’re local to the measure or query.
Syntax:
VAR <varName> = <expression>
VAR <varName2> = <expression2>
...
RETURN <final_expression_using_vars>
You can define one or multiple VARs, and then after the RETURN keyword, put the expression that produces the final result (usually involving those variables). In the final expression, you refer to the variable by name (without any special quoting).
Benefits of variables:
VAR Intermediate = ... RETURN ... to make it clear what each piece represents.VAR totalRows = COUNTROWS(Sales) then use totalRows twice is better than writing COUNTROWS(Sales) in two places (DAX Best Practices | MAQ Software Insights). This not only avoids writing it twice, but also ensures the value is identical and computed once (which can improve performance).Example of using VAR for performance:
BigRatio =
VAR totalOrders = COUNTROWS( Sales )
VAR totalSales = [Total Sales] -- assume [Total Sales] is defined elsewhere
RETURN
IF( totalOrders = 0,
BLANK(),
totalSales / totalOrders
)
In this measure, we calculate totalOrders (number of sales) once. We also capture [Total Sales] into totalSales variable (technically if [Total Sales] is a measure, calling it multiple times would give the same result in the same filter context – the engine might already optimize it, but using a VAR guarantees it’s only evaluated once here). Then we return an IF that uses those variables. If we did not use variables, we might write IF(COUNTROWS(Sales)=0, BLANK(), [Total Sales] / COUNTROWS(Sales)). In that original form, COUNTROWS(Sales) would be evaluated twice (once for the IF check, once for the division). Using VAR, it’s evaluated once (DAX Best Practices | MAQ Software Insights). This is a trivial example, but with heavier expressions this matters a lot.
Debugging with variables:
Let’s say you have a complex measure and you suspect the problem is in one part of it. You can refactor:
ComplexMeasure =
VAR part1 = ... some expression ...
VAR part2 = ... another expression possibly using part1 ...
RETURN part2 -- temporarily return part2 to see if it's right
If the result of the measure in a visual seems off, you might set RETURN part1 just to see what part1 is producing in that context. This way, you isolate which part is incorrect. Another trick: sometimes output multiple variables by combining them in a table or concatenated string just for debugging purposes, though in a measure you can only return one scalar value – but you could make a temporary calculated table measure or use DAX Studio to run a query to see multiple variables. There is also a feature in tools like Tabular Editor or DAX Studio to evaluate intermediate steps.
(DAX Debugging Tricks ( Basic to Advanced ) | Power BI! - Medium) indicates: Variables in DAX help split complex formulas into manageable pieces and allow intermediate results to be reused. This captures why they are good for both debugging and performance.
Example of a complex measure using multiple variables:
Suppose we want a measure “% of category sales that are current product’s sales”. This could be done in steps:
Product Sales % of Category =
VAR currProductSales = [Total Sales] -- sales for the current product (filter context)
VAR categorySales = CALCULATE( [Total Sales], ALL( Product[ProductName] ) )
-- remove product filter, so it's total for the category (assuming Category is still filtered)
RETURN
DIVIDE( currProductSales, categorySales )
Here we used two variables to clarify our intent: one for the current product’s sales, one for the whole category’s sales (we removed only the product filter, so category context remains). This makes the measure easier to read and double-check. We could even debug by returning categorySales alone to ensure it’s doing what we expect.
RETURN trick are your best friend to understand complex logic. SQLBI’s article on debugging DAX measures suggests techniques like returning table constructs or using the ERROR() function cleverly, but those are advanced. Usually, incrementally building the measure and testing as you go is the best approach.Tip: Use descriptive names for variables (and measures). This self-documents your code. For example,
VAR avgSales = DIVIDE([Total Sales], [Order Count])is clearer thanVAR x = .... While DAX variables can’t be viewed outside, the measure definition itself becomes easier to understand when you or someone else revisits it. Also, keep in mind a variable is calculated once per filter context (not once per row, unless used inside an iterator per row). If you need a per-row calculation, you still need an iterator or a calc column.
As you get into more complex DAX scenarios, it’s important to follow best practices to ensure your measures run efficiently and your results are correct. DAX is powerful, but performance can suffer if formulas are not optimized or if the model is not designed well. Here are some key practices and tips:
[Result] = IF(X > 0, X/total, X) where X is some complex sum, do VAR X = complex sum RETURN IF(X > 0, X/total, X). This way complex sum runs once. Similarly, you can create helper measures for reusability. E.g., define [Total Sales] once and reuse it in many other measures rather than writing SUM(Sales[Amount]) in every measure (and possibly adding filters each time incorrectly). This modular approach also makes maintenance easier. (DAX Best Practices | MAQ Software Insights) illustrated how using a variable prevented double calculation of a measure.Use Built-in Functions Optimally:
DIVIDE(numerator, denominator, alternateResult) instead of the / operator when dividing measures, to gracefully handle division by zero without extra IF logic (DAX Best Practices | MAQ Software Insights). It’s both clearer and potentially a tiny bit more efficient than doing an IF yourself.SELECTEDVALUE(column, alternate) instead of the pattern IF(HASONEVALUE(column), VALUES(column), alternate) (DAX Best Practices | MAQ Software Insights) (DAX Best Practices | MAQ Software Insights). SELECTEDVALUE does exactly that check internally (returns the single value if one exists, otherwise the alternate or BLANK). This makes code cleaner and potentially avoids performance issues of accidentally getting an error from VALUES() when multiple values exist.CONCATENATEX for string aggregations instead of trying to stitch strings via hacks (like using PATH functions or so).COALESCE() (as of newer updates) to handle blanks in a cleaner way than nested IFs for default values.FILTER inside CALCULATE. Recall that giving a filter like Table[Column] = value directly is usually faster than using FILTER(Table, Table[Column]=value) because the latter iterates the whole table. If you have multiple conditions on the same table, you can often combine them in one FILTER. If you want to preserve an existing filter and add a new one, consider KEEPFILTERS instead of FILTER (DAX Best Practices | MAQ Software Insights). For example, CALCULATE([Measure], KEEPFILTERS(Table[Col] = "X")) will respect existing filters on Col and require "X" too, whereas FILTER would disregard the existing filter on Col. This can both affect correctness and performance (because of how queries get optimized).FILTER(Sales, ...) or SUMX(Sales, ...) will iterate all those rows in the formula engine, which can be slow. If you can push a filter to the storage engine (VertiPaq) it’s faster. E.g., CALCULATE([Measure], Table[Col] = "Value") will let the engine use its internal indexes to filter, which is usually very fast. But FILTER(Table, Table[Col]="Value") would enumerate rows one by one. So use iterator functions judiciously. When possible, structure your measure using CALCULATE with filter arguments or use COUNTROWS( FILTER(...) ) patterns that at least limit the rows early.COUNTROWS( DISTINCT( Sales[CustomerID] ) ) for a huge sales table, you could have a Customers table and do COUNTROWS( FILTER( Customers, [Total Sales for Customer] > 0 ) ) where [Total Sales for Customer] is maybe a calc column or measure. The latter might iterate fewer rows (just customers). This depends on model and need, but think about what you’re iterating over.Common Mistakes to Avoid:
VALUES() in a context where it can return multiple values without wrapping in an aggregator or SELECTEDVALUE; it will error if multiple values appear. If you expect one value but sometimes there are many, use SELECTEDVALUE or an iterator to handle it.ALLSELECTED vs ALL – ALLSELECTED is context-sensitive (it respects outer filters but removes filters from the current visual context), can be confusing for percent of total calcs. Use ALL when you mean truly all, ALLSELECTED when doing total of the selection (for subtotals etc.).SUMX(Filter(Sales,...), Sales[Amount]), consider if CALCULATE(SUM(Sales[Amount]), ... ) could do the job with less explicit iteration. The DAX optimizer might do similar things under the hood, but writing it in a set-based way often aligns with engine optimizations.In short, write DAX clearly and efficiently. Favor the powerful filter context over manual row iteration when possible. Test your measures with small filters to ensure they compute expected results before applying to big data. And remember, DAX is a mix of a query language and formula language – sometimes there are multiple ways to achieve something (set-based vs procedural). Aim for the approach that leverages the strengths of the VertiPaq engine (set-based filtering, compression) rather than brute-force calculations.
Finally, stay curious and keep learning: DAX has many nuances and functions (over 200 functions). Refer to official documentation and community blogs for best practices. Over time, you’ll develop an intuition for writing DAX that is not only correct but also performant and easy to maintain. Happy DAX-ing!