1. Cross-filtering and role-playing Dimensions
Let's cover bi-directional cross filtering and role-playing dimensions in Power BI.
By definition, bi-directional cross filtering, in contrast to single cross filtering, allows you to filter on both sides of the table relationship. Let's illustrate with an example. In our fact table, the data ranges from 1978 to 2018. Suppose we have a part of a report that has slicers for both Year and Establishment Age. Since the fact table doesn't contain data of the years 2019 and later, the end user shouldn't be allowed to see establishment ages when filtering for the year 2019 and onwards. In the model view, you can see that there is no filter that goes from this fact table towards the Age dimension.
To change this single directional relationship to bi-directional, double click the relationship arrow and set the cross filter direction to both. You can see that the arrows are now pointing in both directions of the table relationship.
If we now go back to the report view, bi-directional cross filtering has filtered out the possibility to select an establishment age for the non-existing years in the fact table. They appear again when I select an earlier year. This allows you to have more control over how end users can apply filters when working with related tables.
Let's go back to our fact table. As you can see there is another Year value present in the data. The Year column represents the year the survey was conducted in, but the Year Founded date refers to the year each establishment was founded. In the data model we can see that there's only one relationship between Time and Establishment Survey, on Year. But what if we wanted to know the average number of employees for establishments founded in the 90s, so by decade? We can't answer that question at the moment. The first thing I'll do to fix that, is create a new relationship between Year Founded and Year. As you can see, the relationship line is dotted, indicating that the relationship is inactive. This is because Power BI doesn't allow for multiple active relationships between tables. But no worries, we can still tell Power BI to use this relationship. Let's go into the Report view. I'm going to create a new measure in the fact table, and call it Number of employees by Year Founded. I'll start by using the CALCULATE() function. For the first argument, I'll take the average of the Number of employees from the fact table. In the second argument, I'll tell Power BI what relationship to use. With the USERELATIONSHIP() function I can specify that I want Year from the Time dimension linked to Year Founded from the fact table. If I now create a table with Decade and the measure I just created, I'm able to see the number of employees by the decade the establishment was founded in.
That's all! Time to try it yourself in the exercises.
2. Let's practice!