Welcome to Part 3 of the It's Complicated: Relationships in Power BI series. If you haven't already read the previous posts, you may review them here:
It's Complicated: Relationships in Power BI Part 1 (Fact vs Dimension)
It's Complicated: Relationships in Power BI Part 2 (Cardinality)
In this final post in the series, we will look at Cross-Filter direction. If you are an Excel user, this post will help you understand the limitations you have in the Excel data model using single Cross-Filter direction. If you are a Power BI user, this post will help you understand why both Cross-Filter direction can create ambiguity in your data model.
To review, we are still working with the standard star schema relationship data model. We have discussed the concept of Fact and Dimension tables, one to many cardinality, and now we need to look at the meaning behind that arrow in the middle of the relationships between our tables.
In the image below, we can see the same Employee Training Attendance report we've been working with throughout this series. The Dimension tables have a 1 next to them, indicating they are correctly identified as a cardinality of 1 in the key relationship column. The Fact table has a * next to it, indicating it has been identified as a cardinality of many in the key relationship columns. But what does the arrow mean?
Cross Filter Direction
The single arrow indicates that these relationships have a cross filter direction of Single. In Power BI we have two choices for Cross-Filter direction:
- Single
- Both
In Excel, we only have the option to apply Single cross filter direction. Good news - single is the default best option for the most common star schema data models.
What does Single Cross Filter direction mean?
Unlike the salmon leaping upstream in the image at the start of this post, your data CANNOT flow against the direction of the relationship. When the cross filter direction is set to single, data can freely flow from the Dimension table into the Fact table, but not in reverse.
In practice, that means that our Fact table has virtually every column from each Dimension table, but that our Dimension tables do not have any columns from the Fact table.
Pro tip: It is also important to note that Dimension tables do not have any columns from other Dimension tables either.
This means we can filter the Fact table by itself, or by any column in any of the Dimension tables. We cannot filter a Dimension table by a Fact table or by another Dimension.
Rule of thumb: If using more than one table in the same visualization, fields in the 'Values' must belong to the Fact table.
In the image above, you can see two matrix visualizations; Count DimCourse[CourseID] and Count FactTrainingAttendance[CourseID].
The Count DimCourse[CourseID] on the top is using the CourseID column from the Dimension table in the Values area of the matrix. This is not allowed when trying to combine with the Gender from a DIFFERENT table because the cross-filter direction is set to single. The Gender can flow into the Fact table, but cannot continue into the DimCourse table, so we are unable to splice and dice the DimCourse table by any other table. DimCourse columns cannot go in the values of a matrix.
The FactTrainingAttendance[CourseID] table on the bottom is using the CourseID column from the Fact table in the Values area of the matrix. This works because the Gender from DimEmployee can flow in the direction of the arrow with our single cross-filter direction and splice and dice the Fact table, so we can count the CourseID column in the Fact table and place this in the values of a matrix.
Avoid Both Cross-Filter Direction in One to Many Relationships
While it may seem tempting to change the cross-filter direction to both to get around this limitation, it is NOT recommended for one to many relationships. It can add ambiguity to your data model. Gender can easily flow into the Fact table - we have 1 gender for each EmployeeID, so know exactly what Gender to place in each row of the Fact table. But what happens when we try to pull Gender into the DimCourse table?
Recall our data tables look like this:
FactTrainingAttendance
| EmpID | CourseID | Date | Result |
| 1 | PB1 | 24-Jun-2020 | 95 |
| 2 | EX1 | 20-Jun-2020 | 70 |
| 1 | PB2 | 30-Jun-2020 | 80 |
| 3 | EX1 | 20-Jun-2020 | 80 |
DimCourse
| CourseID | Course Name | Course Category | Level |
| PB1 | Power BI Essentials | Power BI | Beginner |
| PB2 | Power BI Next Level | Power BI | Intermediate |
| EX1 | Excel Essentials | Excel | Beginner |
DimEmployee
| EmpID | Name | Date of Birth | Gender |
| 1 | John | 24-Jun-1960 | M |
| 2 | Paul | 20-Aug-1988 | M |
| 3 | Mary | 14-Feb-1975 | F |
So if we try to put Course Name in values, and Gender in Columns, we want to know how many courses were attended by each gender. That sounds simple, we just need to figure out which Gender to put in the DimCourse table for each course. Let's look at Power BI Essentials. In our small data sample, this was attended by EmpID 1 only. EmpID 1 = Male (John). Ok, 1 for Males. Power BI Next Level was also attended by only one employee, the same EmpID = 1. Ok, 2 for Males. Excel Essentials was attended by two employees - EmpIDs 2, 3. EmpID 2 is Male, but EmpID 3 is Female. What gender can we assign to Excel Essentials? It is ambiguous. Therefore, the cross-filter direction should stay set to single for a one to many relationship.
