In this post I will give a concrete example of why Cross-filter direction of 'both' can cause errors.
Cross-filter direction
Let's start by defining Cross-filter direction. This is the direction that filters propagate through your data model. In plain English, if the Cross-filter direction is set to single from DimTable to FactTable (which it should be), then we get the following behavior:
- putting a filter on the DimTable will filter the FactTable
- putting a filter on the FactTable will NOT filter the DimTable
As we saw in the video above, this behavior can be annoying at times - we want to filter our DimTable to remove records with no entry in the FactTable. However, the problem arises when we have many DimTables. Let's look at the example below.
In the above image, let's focus on the three tables at right:
- OlympicMedals (DimTable1)
- OlympicTeams (DimTable2)
- Tokyo 2020 Medal Standings (FactTable1)
You can see that the Cross-filter direction between DimTable1 and FactTable1 is set to both. This means that if we filter DimTable2, it will filter both the FactTable1 AND the DimTable1.
In this example, if I choose a specific team, for example the United States of America, that will filter the Tokyo 2020 Medal Standings table. Since the Team USA haven't yet won any medals in Tokyo 2020 at the time of writing, the Tokyo 2020 Medal Standing table is now empty.
With a Cross-filter direction of both between Tokyo 2020 Medal Standing table and OlympicMedals table, the Tokyo 2020 Medal Standing table filters the OlympicMedals table, so it is also empty. This causes my slicer for Medal Type to be empty:
Let's look at another country; Japan was the first team in Tokyo 2020 to win two medals. At the time of writing this post, they have earned a Gold and a Silver medal. In the image below when Cross-filter direction is set to both, you can see that my slicer for MedalType[Medal Type] now shows only two medals; Gold and Silver. This table has again been filtered by the Tokyo 2020 Medal Standings table.
Single Cross-Filter Direction
To fix the slicer, we need to set the Cross-filter direction to Single. This should be your default setting for a one to many relationship.
Now our data model looks like this:
When we return to the report, our United States Olympic Medal History page is fixed - the Medal Type slicer has restored Gold, Silver and Bronze as options.
