Power BI - Understanding Cross Filter Direction with a simple example

When loading data to Power BI, it automatically detects Relationships between loaded tables and set the Cardinality and Cross Filter Direction. The Cardinality refers the relationship between two tables such as Many to One or One to Many BI or  One to One and Cross Filter Direction decides how tables are treated in visualizations in reports.

There are two types of Cross Filter Direction: Both and Single. When the relationship is Both, two tables linked are treated as a single table for aggregation data in visualization. This means, both tables can be used for aggregation, one table is for aggregation and the other is for filtering. When it is set to Single, the lookup table or the table used for filtering cannot be used for aggregation.

Let's take an example and see how it works. Here are the steps for testing this.

1. Open Power BI and connect with SQL Server AdventureWorksDW2014 database.

2. Import five tables: DimCurrency, DimProduct, DimProductCategory, DimProductSubCategory and FactInternetSales. Power BI automatically detects the relationships and tables will be shown as below;

3. Double click on any relationship and see the set Cross Filter Direction. Let's see the relationship between DimCurrency and FactInternetSales.

As you see, Both has been set as the Cross Filter Direction. This is the default for many relationships, however, for certain scenario, it will set the it as Single. For example, when you load these tables, if you load DimDate as well, you see that all relationships are set to Single. The reason for that may be, DimTable is Role Playing Dimension, there are three links between this table and the fact.

4. For testing purposes, let's make all Cross Filter Direction as Single.

5. Now let's go to Report section and add a Table widget. Let's add SalesOrderNumber from FactInternetSales and CurrencyName from DimCurrency table.

6. Let's add another table that shows EnglishProductCategoryName from DimProductCategory and CurrencyName from DimCurrency table.

7. As you see, first tables shows currency used for each order and second shows currencies used with each category. Let's try to get the number of currencies used instead of the name, means going to perform an aggregation over DimCurrency.

8. Let's create a measure called NofCurreciesUsed. Go to Data section and add a measure like below.

      NofCurreciesUsed = COUNT(DimCurrency[CurrencyName])

9. Let's go back to the Report and change both tables. Remove CurrentName from both tables and add newly created Measure. This is what you should see once it is added.

10. As you see, Aggregation over the DimCurrency is not working as expected. The reason for this is set Cross Filter Direction. The DimCurrency table cannot be aggregated using FactInternetSales (link is set via it) because Cross Filtering is not enabled. Let's change the Cross Filter Direction between DimCurrency and FactInternetSales back to Both.

11. Now you should see the the correct values in both tables.

This is it works. For more information read: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/