DAX HANDBOOK
5.4 Propagation

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.

Filter propagation represents the ability of filters to flow through the data model.
When we talk about filter propagation in a data model, there is only one consideration we need to consider, and that is whether we are in a row or filter context. If we are in a filter context, all filter propagations happen automatically. If we are inside of a row context (calculated columns and iterative functions), then there is no automatic filter propagation, but instead, we need to use a specific set of functions to force the propagation. Those functions are RELATED or RELATEDTABLE (depending on which side of the relationship we wish to propagate filter).

Propagation in Filter Context

TotalQuantity = SUM(FactSales[OrderQuantity])

There is nothing special happening here. If we focus on the [TotalQuantity] for the cell Clothing-Canada, the Measure automatically accepts filters coming from DimProduct and DimCustomer table and performs

SUM(FactSales[OrderQuantity]) over the filtered FactSales table.

Propagation in Row Context

As opposed to propagation in filter context, in row context, you need to activate relationships every time you need to access data from other tables. Let’s observe the following calculation.

StandardCostAmount =
SUMX(
FactSales,
FactSales[OrderQuantity] * RELATED( DimProduct[StandardCost] )
)

Now let’s debug the calculation for the rounded blue cell.

  1. Filters Bikes and Australia automatically filter through relationships and filter the FactSales table.
  2. SUMX creates iteration over filtered FactSales table.
    a. SUMX creates row context iteration over FactSales table
    b. X part of the calculation is being executed in a row context
    X part = FactSales[OrderQuantity] * RELATED( DimProduct[StandardCost] )

Because X part of the calculation is evaluated in a row context, we cannot simply pull the data from tables other than the FactSales one. Since we need to pull [StandardCost] column from DimProduct table, we must use RELATED function which activates relationship and pulls the data.

Important to notice is that SUMX created a row context over the FactSales table, and in that iteration we wanted to access the data from DimProduct which is on a 1 to many side of the relationship with FactSales table. Because of that, in SUMX iteration, for each row of the iteration, we are certain that only a single value (row) can be pulled from the DimProduct table. Therefore we can use RELATED function. If there could be multiple rows of data returned, we would need to use RELATEDTABLE function to return all corresponding rows.
The rule is the following:

  • If the row context is trying to pull data from 1 side of a relationship, you will use RELATED
  • If the row context is trying to pull data from * side of a relationship, you will use RELATEDTABLE

The easiest way to explain RELATEDTABLE is through calculated columns. Let’s say we wish to add a calculated column to DimProduct table, in which we want to calculate the number of transactions for each product key.

We will first try the calculation without the RELATEDTABLE function

NumberOfTransactions = COUNTROWS(FactSales)

We receive the same figure for every row of iteration. The value returned is the total number of rows in a FactSales table. For each row of the iteration, because we want to access data from the FactSales table while in row context, no filter propagation occurs. Because of no propagation, for each row of the iteration, the calculation sees the whole fact table and counts its rows.

If we add the RELATEDTABLE function, the formula starts to work as expected.

NumberOfTransactions = COUNTROWS(RELATEDTABLE(FactSales))

RELATEDTABLE activates the relationship between DimProduct and FactSales table, therefore for each row of the table DimProduct, the related table (FactSales) gets filtered by the value of the current row of the DimProduct[ProductKey] column and returns only rows from FactSales that survive the condition. Because it returns rows instead of a single row, we need to use RELATEDTABLE instead of RELATED.

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

Advanced Excel training for client Hilding Anders

We have started our autumn training sessions! We are starting with a dear client from Međimurje, Hilding Anders, where we’ll deliver an advanced Excel training for the logistics and production departments. The training will last a total of 16 hours, split into 2-hour sessions. Advanced Excel is useful for all...

Read more

Napredna Excel edukacija za klijenta Hilding Anders

Krenuli smo s jesenskim edukacijama! Edukacije započinjemo kod dragog klijenta iz Međimurja, Hilding Anders, gdje ćemo za odjele logistike i proizvodnje proći kroz naprednu Excel edukaciju. Edukacija će trajati sveukupno 16 sati u terminima po 2 sata. Napredni Excel koristan je svim polaznicima koji svakodnevno rade sa velikom količinom podataka,...

Read more

Business improvement with the help of Power BI

How did we improve our client’s business with the help of Power BI? A global brand with a branch in the Adriatic region had several problems: • Uncollected receivables• Sales without margin (price lists in disarray)• Hidden losses• Unprofitable product lines and stockpiling• Lack of focus in sales and procurement,...

Read more

Unaprijeđenje poslovanja pomoću Power BI-ja

Kako smo uz pomoć Power BI-ja unaprijedili poslovanje našeg klijenta? Globalni brand s podružnicom u Adriatic regiji je imao više problema: Nenaplaćena potraživanja Prodaja bez marže (cjenici u rasulu) Skriveni gubici Neprofitne linije proizvoda i gomilanje zaliha Nije bilo fokusa u prodaji i nabavi već se sve obavljalo stihijski Zašto...

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