Power Bi Dax Common Patterns

This deck walks you through the DAX patterns you'll reach for again and again in Power BI. It starts with the foundational questions, like the difference between a calculated column and a measure, and then builds into the formulas that drive real reports, including totals, distinct counts, year-over-year changes, year-to-date totals, and percent-of-total calculations. You'll also practice the workhorse functions of DAX, such as CALCULATE, ALL, ALLSELECTED, and the iterator family (SUMX, AVERAGEX, and similar), so you start to recognize which one to pull up for a given problem.

It's a great fit if you already know your way around the Power BI desktop, can build a basic visual, and now want to move beyond dragging fields onto a canvas into writing your own measures. If you're newer to DAX but comfortable with Excel formulas, these cards can still work for you, as long as you're willing to experiment in a practice file alongside the deck.

DAX really clicks when you see patterns repeat, so try to connect each card to a real report you've built or imagined. When you study, mix up the order rather than going front to back in one sitting; your brain benefits from retrieving the formula in slightly different contexts, which is exactly the kind of active recall this deck is designed to support.

A practical tip as you work through the cards: keep DAX Studio or a simple test visual open so you can verify each pattern with a small example, because seeing the result of a measure on real numbers is the fastest way to build intuition for things like filter context and the difference between ALL and ALLSELECTED.

The most important distinction in DAX is between a calculated column and a measure. A calculated column is evaluated row by row at refresh time, stored inside the model, and operates under row context. A measure is evaluated at query time inside the current filter context, typically aggregates values, and never bloats the model. Because measures compute on demand, they reflect whatever filters a visual or slicer applies. Best practice is to prefer measures whenever possible and reserve calculated columns for cases where you genuinely need the result as a slicer, filter, or row-level attribute.

Context in DAX comes in two flavors. Row context means "this current row," and it is created automatically by iterator functions such as SUMX, or when you write an expression inside a calculated column. Filter context is the set of filters currently applied to the model, originating from visuals, slicers, or the CALCULATE function. When you place a measure inside a calculated column, row context transitions into filter context: every column value on the current row becomes a filter on its respective table. This context transition is powerful but surprises beginners, and triggering it unnecessarily inside a calculated column is a common performance pitfall.

Basic measures are built from aggregation functions like SUM and DISTINCTCOUNT. SUM(Sales[Amount]) totals a column across the current filter context, while DISTINCTCOUNT(Sales[CustomerId]) counts unique customer identifiers. For division, prefer the DIVIDE function over the slash operator because DIVIDE handles a zero denominator gracefully by returning BLANK (or an optional third argument), whereas the slash operator produces an infinity error. Understanding these building blocks, plus the contrast between row and filter context, sets the stage for everything else in DAX.

CALCULATE is the single most important function in DAX. It evaluates an expression under a modified filter context, replacing or adding filters as specified in its arguments. A pattern like CALCULATE([Total Sales], Products[Category] = "Bikes") overrides any existing filter on Category. By contrast, KEEPFILTERS makes a filter additive: it intersects with existing filters rather than replacing them, which is useful when you want to layer a condition without clobbering the user's selection.

Several functions exist to remove filters deliberately. ALL(table[column]) clears filters on the specified column entirely, ignoring even slicers. ALLSELECTED() respects the user's slicer choices but ignores row context within a visual, making it the go-to choice for percent-of-total measures in visuals. ALLEXCEPT(table, column1, column2) clears all filters on the table except those on the listed columns. REMOVEFILTERS is essentially a convenience wrapper around CALCULATE(expr, ALL(...)) that reads more naturally. When you need a partial clear combined with logic, write FILTER(ALL(...), condition) instead of plain ALL, because ALL alone removes every filter.

Virtual tables can apply filters even when no physical relationship exists. TREATAS remaps columns from an in-memory table to a real model column, which is invaluable for many-to-many scenarios or for applying filters from disconnected parameter tables. INTERSECT and EXCEPT perform set operations: INTERSECT returns rows common to both tables (for example, customers who bought in both 2024 and 2025), while EXCEPT returns rows in the first table not present in the second (active customers excluding churned ones). For cross-table scalar lookups, RELATED pulls a value from the one side of a relationship and RELATEDTABLE returns a child table. LOOKUPVALUE performs a row-by-row lookup ignoring relationships; it is convenient but slower than RELATED and should be avoided in hot paths.

Time intelligence in DAX requires a properly configured date table. Mark a table as the date table in the Modeling tab, ensure it contains a continuous, unique list of dates with no missing values, and then build relationships from fact tables to this date dimension. Without this prerequisite, time intelligence functions may produce unreliable results.

Standard period-to-date measures are simple wrappers around CALCULATE. DATESYTD returns dates from the start of the year up to the current context, giving you a year-to-date measure. DATESQTD and DATESMTD perform the same role at quarter and month granularity. For fiscal years that do not align with the calendar, pass a year-end date as the second argument: DATESYTD('Date'[Date], "06/30") gives fiscal-year-to-date for a fiscal year ending June 30. The DATESBETWEEN function returns a table of dates between two boundaries and is useful for custom date windows that do not fit a named function.

For comparing to prior periods, SAMEPERIODLASTYEAR returns a table of dates shifted by one year at the same grain as the current context, skipping dates that did not exist in the prior year. DATEADD shifts the existing period grain by a number of intervals and is more flexible, supporting months, quarters, and days. PARALLELPERIOD always returns the full period at the level specified; for example, even if you are filtered to mid-month, PARALLELPERIOD at the month level returns the entire prior month. A common rolling-window pattern is DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -30, DAY), which yields the last 30 days from the current maximum date. Combine this with AVERAGEX or SUMX to produce rolling averages or trailing totals like trailing 12 months. Be aware that only one time intelligence filter can apply at a visual level at a time; when you stack YTD and PY logic naively, one filter wins. Calculation groups solve this elegantly.

Iterator functions evaluate an expression for each row of a table and then aggregate the results. SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) multiplies quantity by unit price for every row and sums the result, which is essential when the value you want to aggregate is not a single column. Other common iterators include AVERAGEX, COUNTX, MINX, MAXX, RANKX, FILTER, and ADDCOLUMNS. Iterators create row context and are the primary mechanism for row-level calculations outside of calculated columns.

Ranking is a frequent analytical need. RANKX(ALL(Products), [Total Sales], , DESC, Dense) returns the rank of each product by sales across the entire product table, ignoring any filters. You can wrap this inside a measure and filter to rank less than or equal to 10 to dynamically produce a "top 10 products" visual. For a rank within a category, filter the table inside RANKX so each row only competes with peers sharing the same category. The PERCENTILEX.INC function similarly takes an iterator and a probability (such as 0.9 for the 90th percentile), making it useful for percentile-based analyses.

Table-shaping functions build virtual tables that you can pass into iterators or use inside CALCULATE. ADDCOLUMNS(Products, "Sales", [Total Sales]) adds a calculated column at query time without bloating the model. SUMMARIZE groups and aggregates, while SUMMARIZECOLUMNS is engine-optimized for visuals and respects filter context automatically, making it the preferred choice inside measures that drive visuals. GROUPBY requires an iterator like MAXX to materialize values and is less common. Set operations include UNION to stack tables with the same schema, EXCEPT to subtract one table from another, and INTERSECT to find common rows. GENERATE differs from CROSSJOIN by evaluating an expression per row of the outer table, allowing a measure to be evaluated per outer row. GENERATESERIES creates a single-column numeric table useful for dynamic axes such as NPS buckets.

Calculation groups let you define reusable transformation patterns that apply to any measure on a visual. For example, a single calculation group can define YTD, prior year, and month-to-date variants that work against whatever measure is on the visual, replacing dozens of branched measures. Inside each calculation item, SELECTEDMEASURE returns the measure originally placed on the visual and SELECTEDMEASUREFORMATSTRING reads its format string. Calculation groups are typically authored in Tabular Editor and are the modern solution to the problem of stacking multiple time intelligence filters, which cannot coexist on the same visual otherwise.

Row-level security restricts data visibility per user. In the simplest pattern, you create a role in the Modeling tab and filter a dimension table by a condition such as [Email] = USERPRINCIPALNAME(). For dynamic RLS that maps users to regions or other attributes, build a mapping table joined to your dimension and filter with [Region] IN VALUES(Users[Region]). USEROBJECTID returns the Azure AD object identifier of the current user and is preferred in cloud environments where the mapping table stores object IDs rather than email addresses. CUSTOMDATA reads a value from the embedding application, useful in OEM or embedded scenarios. Avoid bidirectional filters for RLS, because they can leak rows when multiple filter paths exist; a security table with a single-direction relationship is safer.

Many common business metrics follow well-known DAX patterns. Average order value is DIVIDE([Total Sales], DISTINCTCOUNT(Sales[OrderId])). Conversion rate divides distinct orders by distinct sessions. Sell-through rate is units sold divided by units sold plus units on hand. Days of inventory on hand divides average inventory by daily cost of goods sold, where daily COGS is total COGS divided by the distinct count of dates. Cohort retention requires a cohort index column assigned at first purchase, after which retention for month N is the count of active customers in cohort month N divided by the cohort size, often built with SUMMARIZECOLUMNS. Pareto 80/20 analysis ranks products by sales, computes cumulative sales up to each rank, and flags products whose cumulative percentage is at or below 80 percent. Dynamic segmentation uses SWITCH on a What-If parameter or a disconnected slicer table to bucket customers or products without hardcoding thresholds.

Best practices for measure authoring begin with avoiding implicit measures. Power BI auto-creates aggregations for numeric columns, which leads to inconsistent behavior, missing format strings, and double counting. Always write explicit measures using functions like SUM or SUMX and assign them a clear format string. Adopt a naming convention such as "Sales | Total" or "Sales | YTD" with a pipe separator so measures group naturally in the field list. Use the DISPLAYFOLDER property for deeper organization, and document each measure in the Description field with its intent, grain, and known edge cases.

Performance tuning starts with avoiding unnecessary context transitions. Wrapping an iterator inside CALCULATE causes row-to-filter transitions on every row and is rarely what you want inside a calculated column. Materialization, the act of forcing a virtual table expression like FILTER into a physical row-by-row result, is expensive; prefer functions that the storage engine can push down, such as SUM, COUNTROWS, and simple iterators over already-filtered tables. COUNTROWS is faster than COUNT because it reads a single column metadata flag rather than evaluating each value's blank status. Avoid DISTINCTCOUNT on large free-text columns; introduce a surrogate integer key when cardinality is high. The FORMAT function returns text and disables numeric aggregation downstream, so apply format strings on the model rather than calling FORMAT inside measures that feed charts.

The DAX Query View, accessible from Power BI Desktop, lets you author and run DAX queries against your model directly, which is invaluable for testing logic and learning. The DEFINE MEASURE block creates a measure that lives only for the duration of the query without altering the model. EVALUATE ROW("X", [Total Sales], "Y", [Customers]) returns a single row with named scalar columns. EVALUATE TOPN(5, Products, [Total Sales], DESC) returns the top five products. ORDER BY works on the final result of the query. Beyond measures, DAX supplies a rich text and statistical function library. CONTAINSSTRING and SEARCH perform substring matching (the latter supporting wildcards), while FIND is case-sensitive and returns the position. TRIM and CLEAN sanitize imported text, UPPER and LOWER transform case, and SUBSTITUTE replaces substrings with optional instance selection. For statistics, MEDIANX and STDEVX.P or STDEVX.S compute medians and standard deviations across iterators, while GEOMEANX is the correct choice for compounded growth rates. Mastering these patterns, naming conventions, and the query view turns DAX from a stumbling block into a precise analytical tool.