Deep dive into advanced DAX and semantic model concepts

In a data model, a blank row is automatically added on the “one” side of a relationship if that relationship is invalid (i.e. there are fact‑table entries with no matching dimension key). This ensures totals remain correct by grouping all orphan fact rows under a blank member. However, limited relationships (such as many‑to‑many or cross‑island relationships) do not create a blank row; in those cases, an unallocated total may appear, requiring special attention.

• Blank row in DAX article for how the blank row affects functions like VALUES vs. DISTINCT.
• Understanding blank row and limited relationships article.

Not all model relationships behave in the same way. Regular relationships (enforcing a one‑side with unique keys) participate in table expansion and blank row logic, while limited relationships do not. Limited relationships arise in scenarios like many‑to‑many joins or DirectQuery cross‑island links, where uniqueness is not guaranteed. These have no blank row enforcement and are resolved differently at query time (often requiring the formula engine to perform joins). It is important to design models to avoid unintended limited relationships and understand their implications on filter propagation and performance.

• Regular and limited relationships in Power BI article for a thorough explanation.
• Relationships in Power BI and Tabular models article for an introduction to different type of relationships.
• Different options to model many‑to‑many relationships article for performance considerations when using bidirectional vs. limited many‑to‑many relationships.
• Bidirectional relationships and ambiguity in DAX article about ambiguity issues produced by bidirectional filters.
• Costs of Relationships in DAX article about performance implications of different types of relationships.

A circular dependency occurs when two or more calculated DAX entities depend on each other in a loop, preventing the engine from determining a calculation order. Common hidden causes include using CALCULATE (context transition) inside calculated columns (making them all depend on each other), or creating relationships that involve calculated columns/tables (often due to the blank row on invalid relationships).

These SQLBI articles explain these scenarios and best practices for breaking such dependency loops:

• Understanding circular dependencies in DAX article.
• Avoiding circular dependency errors in DAX article.

An expanded table refers to the concept that each table in a model implicitly includes all columns from tables related to it via many‑to‑one (*:1) relationships. In DAX, filter propagation can be understood through expanded tables: filtering a dimension automatically filters the fact table because the fact’s expanded table contains the dimension’s columns. Expanded tables are not materialized in memory but are a logical construct defining how relationships make columns accessible (e.g. the RELATED function works by accessing expanded table columns rather than “following” relationships).

• Expanded tables in DAX article.
• Context Transition and Expanded Tables article shows interactions with CALCULATE.

Data lineage in DAX refers to the hidden metadata that tracks the original source column of any value, even after transformations have been applied. DAX remembers which table and column a value came from, and uses this lineage to apply model filters correctly. For example, a table expression like VALUES(Product[Category]) returns a list of categories that still retain the ability to filter the Product table (and related tables) because of lineage. If you break data lineage (for instance, by concatenating a value with an empty string), the resulting values lose the filter capability. Understanding this is crucial for functions like TREATAS or complex virtual relationships.

• Understanding data lineage in DAX article.
• Replacing relationships with join functions in DAX article for practical use of lineage in joins.
• Set functions in DAX: UNION, INTERSECT, and EXCEPT article for implications of set functions with data lineage.

When a report uses multiple fields, DAX only returns combinations of values that actually exist in the data – a feature known as auto-exists. Notably, auto-existence is applied within a single table: if you put two columns from the same table on a visual, nonexistent combinations are automatically removed from the results. However, with fields from different tables, the engine initially performs separate summarizations (grouping by each table) and then a cross join, relying on non‑empty (driven by measure values) to remove blank combos. This “clustering” by table can lead to surprises, such as seeing unexpected combinations when no measure is present or when using Show items with no data. Power BI employs tricks (like adding an implicit COUNTROWS measure as a filter) to avoid showing a full cross‑join in visuals without measures.

• Understanding DAX Auto-Exist article introduces the auto-exists concept.
• AutoExist and Normalization article demonstrates auto-exist in denormalized vs. normalized schemas.
• Understanding the “can’t determine relationship between the fields” error article explains how Power BI clusters filters and uses non-empty to reduce results.

A shadow filter context is a special, initially inactive filter context that an iterator can create and that only certain functions (notably ALLSELECTED) can activate. When an iterator (like SUMX) runs, it takes note of the current filters on the iteration target as a shadow copy, but does not apply them unless needed. ALLSELECTED leverages this by restoring the shadow context of outer iterators to compute things like totals as if certain filters were not applied. This is key to understanding ALLSELECTED’s behavior in visuals (e.g. computing percentages of page totals while respecting slicers). The concept is advanced, but essentially the shadow context tracks filters present before an inner context – allowing certain calculations to “remember” outer filters.

• The definitive guide to ALLSELECTED article for more details about the shadow filter context.
• ALLSELECTED best practices article for guidance on using this function safely.

CALCULATE (and CALCULATETABLE) have a non-intuitive evaluation order: all filter arguments are evaluated before the inner expression is evaluated, regardless of their position. This means in CALCULATE([Measure], Filter1, Filter2), the engine computes Filter1 and Filter2 in the original context, then applies them to a new filter context, then evaluates [Measure]. In nested CALCULATE scenarios, outer filters can override inner ones. For example, CALCULATE( CALCULATE([M], Country="Italy"), ALL(Customer[Country]) ) will first remove country filters (ALL) then apply Italy, effectively ending up with only Italy. But swapping the two filters would yield a different result by applying Italy first and then removing it. Understanding this execution order is crucial for writing accurate DAX, especially when using KEEPFILTERS or attempting to control the evaluation sequence.

• Order of Evaluation in CALCULATE parameters for common pitfalls and examples of how nesting CALCULATE and filter modifiers interact.

Implementing model security in DAX (Row‑Level Security and Object‑Level Security) introduces additional filter contexts that developers must account for. RLS filters are applied as invisible queries before your measures run, effectively adding extra filter context on tables. You must ensure calculations (especially percentage of total or rank measures) still behave correctly when security roles hide portions of data. There are also DAX functions like USERPRINCIPALNAME() to identify the user, or techniques to dynamically filter based on usernames or group tables for dynamic RLS. Advanced scenarios include handling inactive relationships under RLS (USERELATIONSHIP has limitations under security roles) and enabling hierarchical or dynamic security where one role’s data “rolls up” to another.

For a comprehensive treatment and additional insights:

• Security in Tabular Semantic Models whitepaper (SQLBI+).
• Implement Non Visual Totals with Power BI security roles article.
• DAX limitations with inactive relationships and RLS article.
• Reading active Power BI security roles in DAX article.
• Security Cost in Power BI and Analysis Services Tabular article.
• Managing hierarchical organizations in Power BI security roles article.
• Hiding measures by using object-level security in Power BI article.
• Computing accurate percentages with row-level security article.

Calculation groups allow you to define reusable calculations (calculation items) that can apply to any measure, dramatically reducing measure proliferation. For example, you can create a Time Intelligence calculation group with items like “YTD”, “YOY”, etc., instead of having separate YTD/YOY measures for every base measure. A calculation item is essentially an expression with a placeholder for the selected measure, which gets evaluated by replacing the placeholder with the current measure. While powerful, calculation groups introduce considerations like precedence (if multiple calc groups apply, their order and scope matter) and context interactions (e.g. format strings or handling no measure selected). It’s recommended to follow best practices (one calc group for time intel, one for other variations, etc., unless you fully understand precedence and interactions).

• Introducing Calculation Groups article for basics.
• Calculation Groups series of articles for advanced design topics.
• Inside Calculation Groups video course and whitepaper.

Visual calculations are a Power BI feature that let you create measures within a specific visual, using the visual’s result set as input. This means you can write DAX in a visual context without adding measures to the model. It enables scenarios like custom calculations for conditional formatting or on‑the‑fly computations that exist only in that chart. Under the hood, visual calculations use a special “visual table” (visible result grid) and functions like EXPAND/COLLAPSE to navigate its groups. They also introduced new functions (e.g. PREVIOUS, NEXT, FIRSTNONBLANK in visual context) and the concept of visual context and visual shape of a query. In practice, this allows things like computing a KPI based on only the visible data in a chart or applying a dynamic format.

• Using visual calculations for conditional formatting article.
• Introducing EXPAND and COLLAPSE article.
• Using EXPAND and COLLAPSE in visual calculations article.
• Introducing VISUAL SHAPE article.
• Computing open orders with visual calculations in DAX article.
• Understanding Visual Calculations in DAX whitepaper.

DAX introduced SQL‑like window functions – namely INDEX, OFFSET, WINDOW, and related helper functions – to simplify calculations that involve relative row context or moving aggregations. These functions operate on a sorted, partitioned table (using ORDERBY and PARTITIONBY clauses) and let you easily retrieve a row by position (INDEX), navigate to a relative row (OFFSET), or define a window frame of rows (WINDOW) over which you can then aggregate. For example, with window functions you can compute “sales for previous month” by an OFFSET of ‑1 on a monthly partition, or a moving average by WINDOW of last 3 months, without complex FILTER logic. A key new concept introduced with these is apply semantics, which means the calculation is applied after the initial evaluation of the table expression, allowing efficient iteration without materializing large interim tables. While extremely powerful, these functions are mainly used in queries or advanced measures, and they come with some limitations (like not usable in all contexts, potential performance costs if misused).

• Introducing window functions in DAX article.
• Introducing the RANK window function in DAX article.
• Understanding apply semantics for window functions article.
• Window Functions in DAX whitepaper.