DAX HANDBOOK
6.5 Expanded tables

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

When using whole tables as filter arguments to CALCULATE, you are using an expanded version of the model, which consists of the table used in the arguments, as well as all other tables in the data model that are linked to that table through a 1-* relationship.

We will explain this concept with a small dataset containing only a few columns.

Basics

We will use 4 different measures and observe their results in the visual:

TotalQTY = SUM(FactSales[OrderQuantity])
ALL_QTY_CustomerKey = CALCULATE([TotalQTY],ALL(DimCustomer[CustomerKey]))
ALL_QTY_BusinessType = CALCULATE([TotalQTY],ALL(DimCustomer[BusinessType]))
ALL_QTY_FactSales = CALCULATE([TotalQTY],ALL(FactSales))

First, we will observe the measure ALL_QTY_BusinessType. This measure uses CALCULATE remove filter argument ALL(DimCustomer[BusinessType]) to ignore the Business type on the visual. This is why there is always the same figure of 205788 shown for all 3 different types of business.

The measure ALL_QTY_FactSales holds more complexity since it operates on the expanded table. Even though we added a single ALL(FactSales) table filter as an argument to CALCULATE function, the measure is also ignoring filters coming from DimProduct and DimCustomer tables. We haven’t explicitly added those tables as filter arguments, but they are included in the ALL(FactSales) as expanded tables linked to the FactSales table through a 1-* many relationships.

The expanded table feature is always present but only gets utilized in specific situations, such as using tables as CALCULATE remove filter arguments.

The expanded table of FactSales one contains all columns from dimension tables connected to Fact table through 1-* relationship. This is why, when we ignored FactSales table using ALL(FactSales) argument in CALCULATE, we also ignored all columns coming from dimension tables. Expanded tables feature expands to every other table that has a 1-* relationship with an already existing one, often known as snowflake term.

Key Columns Explained

If we observe the ALL_QTY_CustomerKey in the visual above, we can see that it returns the same values as the TotalQTY measure. We might think that because we ignored a key column as an argument to CALCULATE, we will activate the expanded table feature, but that is not the case.

ALL(DimCustomer[CustomerKey]) will ignore the key column coming from the FactSales table, but will not propagate to the DimCustomer dimension or ignore DimCustomer[CustomerKey] column, even though it is a key pair on the 1 side of the relationship!

Only the complete table reference will expand filter to all dimensions on the 1 side of the relationship.

Materials

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top