Power BI

Relationship Characteristics of a Data Model

Data Modeling is one of the core topics discussed in our advanced Power BI training in Sydney and Melbourne. Without the correct data model, analytics can merely add any value. Data model consist of the right number of tables with the right relationship between them. If you are new to the concept of data modeling in Power BI, we recommend that you go through this blog on Data Modeling:

Relationships provide the connection or link between two tables.

Relationships are defined in Power BI based on the following rules:

  • They take place between 1 column to another.
  • No multi column relationships exists.
  • No self-referencing relationships exists.
  • Tables cannot have 2 active relationships. Only 1 active path can exist between two tables.

Relationships also provide the direction for filter propagation in Power BI. Here’s a guide to understand the filter propagation in Power BI:  https://powerbitraining.com.au/data-modeling-filter-propagation-in-action/

Each relationship has a few properties which defines the relationship.

Cardinality

Cardinality determines if it’s a fact or dimension table. Cardinality of a relationship can be one-to-one, one-to-many or many-to-many.

Filter Direction

Filter direction can take on two values either singe or both. Filter Direction if set to single value allows the flow of filter in one direction. The filter flows from the dimension to the fact table. It cannot filter across two dimensions and provides optimal performance. On the other hand, Setting the filter direction to both makes it as a one big table and also allows to filter data between two dimension tables. This can however have performance issues if data model is huge and it also can create loops if multiple fact tables exist.

Data Modeling in Power BI training in Sydney

If the filtering is turned on to both directions, Power BI treats both sides of the filter as a single query. For example, the above data set consists of three tables however it is considered as a single query. A ‘single’ direction is indicated by a single headed arrow. Where as, the ‘both’ direction is indicated by a double headed arrow.

Apply Security Filter in Both Directions

This option is only relevant if you have row level security in your model. Row Level Security filtering uses single direction filtering regardless of whether relationships are set to single or both. You can manually enable bi-directional cross-filter with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox.

Active

Only one active relationship can exist between two tables. It is necessary to ensure that filtering is done correctly.

Assume Referential Integrity

This option is only enabled when connecting to data using the Direct Query method. For referential integrity to work smoothly, the date column must not contain null or blank values and for each “from” column there must be a “to” column. In this context, the form column is the Many in a One-to-Many relationship. 

Towards the end, we would suggest that creating correct relationships is not the only requirement for a good data model. Good data modeling practices must be followed to ensure problem-solving.

Here’s a quick recap of the best data modeling practices used in Power BI: https://powerbitraining.com.au/best-data-modeling-practices-power-bi/

Conclusion:

Data Modeling is one of the major skills required for efficient data analytics. A data model consists of the right number of tables with the correct relationship between them. There are certain rules to define the relationship between two tables. In this blog post, the concept of relationship in data modeling, the rules followed while creating relationships and the various parameters that define a relationship in data model have been discussed.

Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.

More Checkouts for Power BI Training in Australia

Back to list

Leave a Reply

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