Cardinality – Designing a Data Model

Cardinality

Power BI relationship cardinality decides which table filters the other. There are three possible cardinalities.

Many-to-one (*:1) and one-to-many (1:*)

This is a relationship where you have one unique instance of each value in a column in one table and many instances of those same values in another table. This is the default type of relationship in Power BI.

Many-to-many (*:*)

In this type of relationship, you have many values in common between two columns in two different tables, so it does not require one side to be unique. This is not recommended. The lack of unique values can lead to confusion and your report developers may not know which value represents which table. In the Resolve many-to-many relationships section later in this chapter, you will learn how to represent a many-to-many relationship with many-to-one and one-to-many.

One-to-one (1:1)

This is the easiest relationship to describe. If you have a unique set of values in a column in a table and another unique set of values in a column in another table, and they are the exact same set of values, you can create a one-to-one relationship. This is not recommended, as you are storing duplicate data, and if your data ever changes, you could end up generating errors unless both tables receive the data change and thus maintain the exact same value sets.

The top table in the dialog box corresponds to the left-hand side of the cardinality. The many-to-one and one-to-many options are identical if the table you want on the one side is in the correct location in the dialog box. For one-to-many, the one side should be listed at the top of the box; for many-to-one, the one side should be the second table listed.

Cross-filter direction

We use relationships to filter data. Cross-filter direction determines whether that filter works in a single direction or both directions.

Single direction

In a single direction, one table in the filter is used to filter the data. For instance, products can filter sales, but sales cannot filter products. For a one-to-many or many-to-one relationship, the one side, the side with the unique values, will be used to filter the many side.

Both directions

If you enable Both, then either table can filter the other. So, in our example, the Products table can filter the Sales table, but the Sales table can also filter the Products table. If we add a third table of Region, we can then use the Region table to filter the Sales table to then filter the Products table. This would allow us to generate a report telling us what products are sold in each region. Be careful about using Both on all your relationships, though, as it may add ambiguity to your model and may also lower performance.

You will not be allowed to create a circular dependency. This means that if we have Both enabled on all our relationships, there’s a strong possibility that eventually you will try to link to a table that’s already referenced by another table that your first table has a relationship with. This might be a table that references a table that then references another table that you have a relationship to. If all those relationships are marked as Both, then when you try to create a new relationship, you will get an error message telling you to deactivate relationships. We will talk more about active and inactive relationships later in this chapter.

You can avoid this by having all relationships marked as Single or by using a star schema. In a star schema, dimension tables only ever have a relationship with the fact table.

Author: Noah Walker

Leave a Reply

Your email address will not be published. Required fields are marked *