DAX HANDBOOK
5.3 Filtering directions

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.

Explanation

When we talk about filtering directions, we are referring to the arrows in the relationships.

The way they are pointing is the way the filters can move through the data model. Most of the time you will have a single way of filtering data, and that is from 1 to many side of the relationship (the left orange arrow in the picture above). You can also allow bidirectional filtering, meaning filters can move from many to one side of the relationship too (the right orange arrow).

Single Filter Direction

Let’s say we want to calculate the distinct number of Cities our products are sold in.
The calculation we will use is

DistinctNumberOfCities = DISTINCTCOUNT(DimCustomer[City])

We will first try with a single-direction filter.

We want to show [DistinctNumberOfCities] filtered by the Colors column coming from DimProduct Table.

The result is always the same number, which is the total number of Cities in the DimCustomer table. Even though there is a relationship between the two tables through the FactSales table, the filters are unable to access the DimCustomer table through the relationship FactSales[CustomerKey] -> DimCustomer[CustomerKey] because the arrow is pointing only in the direction of the FactSales table. This means that filter can travel from DimCustomer to the FactSales table, but the opposite direction is not possible, so the filter reaches the FactSales, but when it has to “jump” onto DimCustomer one, it is stopped. Since no filter reaches the DimCustomer table, the values for each color are DistinctCount of DimCustomer[City] with no filters applied.

Setting Cross-filter Direction to Both

Now let’s change the direction between the tables and observe the results.

This time the calculation shows the expected figures. The filter from the DimProduct table can reach the DimCustomer table and is able to filter its row so that only the ones corresponding to a specific color survive. We will now explain in detail the internals of the filter flow for the [DistinctNumberOfCities] calculation. We will focus only on the row in the visual containing filter “Black”.

The Black filter on the Color column filters the DimProduct so that only black color rows survive.

In the Fact table, Product keys that survived condition “= Black” color cross filter CustomerKeys column. Only CustomerKeys of products with Black color will survive and will form a list of CustomerKeys which will jump to the DimCustomer table and filter it.

Crossfilter is a special DAX capability to form a list of active values for column based on filters applied to different columns of the same table. In the example above. We can see that black color will filter out certain ProductKeys from FactSales table. Those product keys will have only corresponding CustomerKeys survive the filtering. CustomerKeys that survive based on ProductKey filter are the one who are being crossfiltered.

After the DimCustomer is filtered so it contains only CustomerKey-s of sales of Black products, does the calculation kick in and count distinct values of the column City of the rows that survived the condition.

The distinct count of the column City for the Color “Black” equals 371 and that number is returned to the corresponding cell in the visual.

Use Cases

Using bidirectional filters is not a good idea, especially on high cardinality columns, since it tends to slow down calculations and can also introduce ambiguity in a data model. It’s often a better idea to always keep the filtering direction from 1 to many sides, and apply bidirectional filters only when strictly needed for specific calculations with the use of CALCULATE function. The same calculation could be written the following way, without the need for applying a bidirectional filter on the relationships.

DistinctNumberOfCitiesCalculate =
CALCULATE(
    DISTINCTCOUNT( DimCustomer[City] ),
    CROSSFILTER ( FactSales[CustomerKey], DimCustomer[CustomerKey], BOTH )
)

CROSSFILTER is a CALCULATE modifier function that can activate different types of relationship filtering directions. Those directions can be:

  • Both
  • None
  • One Way
  • One Way left to right
  • One Way right to left

The advantage of using CROSSFILTER function instead of activating bidirectional relationship is flexibility to use bidirectional filters only in specific calculations and without potential negative impact on the whole data model.

Active vs Non-active

Data Model takes good care of relationships, especially with regard to Ambiguity. Ambiguity means that filters can travel from one table to the other through 2 or more different paths. In the picture below we can see that there are two possible paths between DimDate and DimCustomer tables. One is through the Fact table, and the other is a direct connection through many 2 many relationship based on Year columns. In this case, the model automatically makes one of the relationships non-active. But we can activate it on demand with the use of CALCULATE + USERELATIONSHIP function modifier.

We will add 2 calculations and observe their results.

NumberOfStoresSelling = COUNTROWS(DimCustomer)
NumberOfStoresOpened = CALCULATE(COUNTROWS(DimCustomer),USERELATIONSHIP(DimDate[Year],DimCustomer[YearOpened]))

[NumberOfStoresSelling] is using active relationships in the model, going through the FactSales table to reach DimCustomer and perform the calculation. [NumberOfStoresOpened] measure uses CALCULATE modifier USERELATIONSHIP to activate the direct M2M relationship between DimDate and DimCustomer, therefore, ignoring the active relationship path.

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