Relationships are among the most fundamental concepts in Power BI (and perhaps all of BI platforms). We have two types of relationship in Power BI: Active and Inactive; and there is a good reason for this. Let's see what are these and how can we use them.
Active relationships (those with solid lines in relationship management part of Power BI Desktop) act all the times, unless you prevent them to do so. This means that they will filter data (affect filter context) and affect final results when you use CALCULATE family of functions, select one or more items in a slicer, put filters anywhere and so.
Inactive relationships, on the other hand, won't affect any of the above-mentioned cases. You won't see any effect on DAX results by default, unless you force Power BI to use that relationship. No slicer(s) will act as if relationship was active, and no filter context(s) will be generated by these relationships.
But why don't we use active relationships in all cases? Sometimes we don't want and sometimes we cannot do so:
- You may want to use a filter context only in some specific cases. In this scenario you can deactivate relationships and activate them only during calculations (will be discussed soon).
- Power BI doesn't allow multiple filtering paths between two tables. This means that you cannot filter table B from A, C from B, D from A and B from D at the same time (each relationship has a filtering behavior which cannot be deactivated during data model design). Power BI will prevent you from activating the last one if you try to build these relationships in order, as this causes multiple filtering paths from A to B.
- There is an option in Power BI relationships called "Cross filter direction (CFD)" which will be discussed later. There is a high chance that you cannot activate another relationship if you set this to "Both" for another relationship, because of possible loops in your data model.
We need to know something else about relationships before we talk about handling them:
- Cardinality: This is direction of relationship. Three cases can happen (in Power BI Desktop RS) when you make a relationship between, say, A1 from table A (left/top) and B1 from table B (right/bottom): A1 values are all unique and B1 has repeated values. In this case you will have 1:n cardinality and A will filter B. This happens between dimensions (A) and facts (B) most of the times. If B1 has unique values and A1 has repeated values, you will have n:1 cardinality. This is exactly previous case, but in opposite direction. If A1 and B1 both have unique values, you will have 1:1 cardinality. This is a very specific and possibly rare case. In this case, both A and B can filter each other.
- Cross filter direction (CFD): CFD defines how A and B filter each other. If it is set to "Single", only 1 side of 1:n relationship can filter n side. If it is set to "Both", both A and B can filter each other. This can help better interactions in visualization part, but you should use it with care. It's better to leave it on Single and change it to Both whenever you have no other way to reach your goals. The point is that you cannot use Single for 1:1 relationships. It is set to Both all the times.
Now let's see which options do we have for handling relationships:
- USERELATIONSHIP: You can use it to activate an inactive relationship during calculations in DAX. But you may face problems in complex data models, especially if activation of an inactive relationships causes some types of loops in data model. This works only in conjunction with CALCULATE, BALANCE and TOTAL family of functions, plus a few other ones. Please read this article to learn more about this function and related remarks.
- RELATED: This brings column value from 1 side of relationship to current side (1 or n). You can use it to bring data from dimension columns into fact columns and use them in calculations. This works only with active relationships and can be used only in calculated columns as it requires a row context.
- CROSSFILTER: You can use this function to change CFD of a relationship. You can force it to "None", causing filtering behavior of relationship to be ignored. This is useful when you want to disable filtering effect of a relationship during a calculation. There is no function like USERELATIONSHIP that can do this (say, DONOTUSERELATIONSHIP!). Please read this article to learn more about this function and related remarks.
I hope this helps someone in managing their relationships - between data! Please let me know your ideas.
So practical🙏🏻