Remember, your data model is just a set of tables linked by a relationship. This relationship could be 1-*, 1–1, *-1 or *-*, each having a unidirectional or bi-directional cross filter. For a unidirectional cross-filter, your dimension table filters the fact table and can’t be otherwise, while for a bi-directional cross-filter, both tables can filter each. Here is an example of a data model showing both cardinalities with a bi-directional relationship between the Sales and Product tables.
*Cardinality is simply the direction of your relationship
If the cardinality of all relationships in the model were unidirectional, the Date, Product and Customer tables would filter the Sales table. The date table cannot filter the customer or product table. Say you are interested in how many of your products were sold in a particular year, not the number of times they were sold that year. Just the list of products that were sold that year. Your guess is as good as mine; I will do a distinct count of the Product key on the Sales table.
Below is an image of the result without the bi-directional cross-filter.
Because the filter direction is from the Products to Sales table, the DAX measure returned a single value of 2517, which is the total inventory in the store.
After enabling bi-directional cross filter
So, the question is, why enable bi-directional filtering when I could get the same result without it?
My answer is, what if your product table contains more information than just the product name and product key, and there is an additional drill down you want to do?
Note that with an excellent model, you can do without bi-directional, but with complex DAX comes complex wahala you didn’t sign up for. If your no. of products is based on the Products table, there will be trouble, as seen above, where I had 2517 for all years.
With just the product key, I can see that as the year progresses, the no. of different products bought increases which is a good thing. I’m not a champion of just a few products when over 2000 other products are in my inventory. This increase could be attributed to better marketing, discounted products etc., but that is not the topic of discussion.
Now I want to drill down into how each product category is fairing yearly. This is where your knowledge of the cardinality of your relationship comes into place because of a direct relationship between the date and product table.
Use case — I want to see which product category was sold each year and how many.
Because I’ve enabled the bidirectional filtering, I’ve indirectly created a relationship between the product and calendar table.
Result below. Note that the count of the product key on the sales table is a distinct count.
If I remove the bidirectional filtering, I get the wrong figures—the same set of numbers each year.
So the question is, how does this work?
Now you have seen what a bi-directional relationship is and how it works.
Cheers mate
Comentarios