Simplified Power BI Model Relationships 101 - Learn More | Hello (2023)

Power BI from Microsoftis a technology-driven business intelligence tool for analyzing and visualizing raw data to present actionable data. It brings together business analytics, data visualization, and best practices to help organizations make data-driven decisions. Due to the capabilities of the Power BI platform, Gartner named Microsoft a Leader in the "Gartner Magic Quadrant for Business Intelligence and Analytics Platform 2019“ in February 2019.

Power BI model relationships show how two or more tables are related. In other words, they show how these tables are related to each other. In particular, model relationships relate a column from one table to a column from another table.

This article delves into Power BI model relationships. It also provides an overview of Power BI.

Index

• What is Power BI?
  • Main features of Power BI
• What are Power BI model relationships?
• Understand Power BI model relationships
• Diploma

What is Power BI?

Power BI is a proprietary business intelligence tool designed for seamless data visualization and analysis. It is part of the Microsoft Power Platform. Power BI is one of the common tools used by organizations to analyze their business data building reports.

It comes with a collection of various built-in software services, apps and connectors that deeply integrate with data sources to deliver immersive visuals, interactive reports and insights. Power BI uses its charts, graphs, and other rich visuals that come with machine learning to easily extract insights from data.

Power BI can also read data from XML files, CSV files, JSON format files, and even web pages, and then turn raw data into interactive information. It is available for desktop, mobile and local servers. Users can create and share their reports with other Power BI users within the organization or with partner companies.

Main features of Power BI

Some of the main features of Power BI are listed below:

• Supports API integrations: Power BI allows developers to easily integrate with other applications and embed dashboards in other software using sample code and APIs.
• custom view: Power BI supports custom visualization libraries that allow users to easily visualize complex data.
• AI support: Users can easily perform data analysis using artificial intelligence. With built-in AI support, users can prepare data, build machine learning models, and discover insights.
• Visualization modeling- With the modeling view, Power BI users can break down complex data into simpler data that helps better understand the data, separate charts, and multi-select objects.
• easy to share: Power BI makes it easy for users to share their reports within teams or organizations, ensuring complete privacy.
• hybrid development: Power BI easily integrates with many third-party connectors, apps, and services widely used by organizations that allow users to connect to multiple data sources.

To learn more about Power BI, clickHere.

What are Power BI model relationships?

Power BI model relationshipsPass column filters from one model table to another. Filters are propagated as long as a relationship path must be followed, which can span multiple tables.

Filters always propagate in the same way and without random variation along relationship paths. However, model calculations that use certain DAX functions can disable relationships or change the filter context of relationships. Power BI model relationships do not enforce data integrity.

See an animated example of how Power BI model relationships get past filters.

The model in this example contains four tables:category, product, year,misale. oTable of categoriesis connected toproduct table, which in turn is linked to thesales desk. osales deskis also linkedyears table. Every relationship is a one-to-many relationship.

A query, possibly triggered by a Power BI card image, requests the total amount of sales for sales orders placed in a single category.gato-a,and for a single year, CY2018. Therefore, theCategorymiannual tablesfilters applied. The category table filter is propagated to theproductsIsolation table of two parentage productscat-aCategory.

filtersproductsThe table is then passed tosaletable, resulting in only two sales lines for these products. Sale of products in the category.cat-aare represented in these two sales lines. The total number of units they have is 14.

At the same time, the filter on the Year table is propagated to thesaleTable, leaving only one sales row for the products in the categorycat-athat were ordered in the yearKJ2018. The query set value is 11 units. Keep in mind that applying multiple filters to a table (such as the Sales table in this example) always carries a risk.miOperation in which all conditions must be true.

Understand Power BI model relationships

• Power BI model relationships: Purpose of the relationship
• Power BI model relationships - relationship properties
• Power BI model relationships - relevant DAX features
• Power BI model relationships - relationship score
• Power BI model relationships - precedence rules
• Power BI model relationships: Performance preference

Power BI model relationships: Purpose of the relationship

Apply star schema design principles

To create a model with dimensions and fact tables, we recommend using the Star Schema design principles. Power BI can be configured to apply rules that filter dimension tables, allowing Power BI model relationships to efficiently propagate these filters to fact tables.

The following model contains adate table, ship date table and delivery date table, all of which have a unique and active relationship with their respective resellerssales deskcolumns.

separate tables

A model table that is not linked to another model table is unusual. TOseparate tableis a term for such a table in a valid model layout. Filters on a separate table must not overlap with other model tables. accept instead"user input"(possibly through visual segmentation) so that model calculations can understand the value. Imagine a table with a series of separate exchange rate values. A measure expression can use this value to convert values ​​to sales as long as a filter is applied to filter on a single rate value.

One Power BI Desktop feature that creates a separate table is theand ifParameter.

Power BI model relationships - relationship properties

Power BI model relationships connect one table column to another table column. Relationships between columns in the same table are not possible. This concept is often confused with the ability to create a self-referential foreign key constraint for relational databases. Parent-child relationships can be stored using this relational database concept (for example, each employee record is linked to a"Reports for"Employees). On the other hand, this type of relationship cannot be used to generate a hierarchy of models.

cardinality

AcardinalityThe type characterizes each of the relationships in the Power BI model. The data properties of the"von"mi"for"related columns are represented by four types of cardinality. EITHER"as"side indicates that the column contains unique values, while side"many"side indicates that the column may contain duplicates.

The four options are listed below along with their abbreviated spellings:

• One to many (1:*)
• Many to One (*:1)
• One to one (1:1)
• Too much (*:*)

The cardinality type is automatically detected and set when you create a relationship in Power BI Desktop. Power BI Desktop runs a query on the model to determine which columns have unique values. It uses internal memory statistics to import models and sends profile queries to the DirectQuery model data source. However, Power BI Desktop can make mistakes from time to time. If the tables are not yet loaded with data, or if the columns you expect to have duplicate values ​​now have unique values, something may go wrong. In either case, you can update the cardinality type if it exists"as"The side columns have unique values ​​(or the table hasn't loaded with data rows yet).

One-to-many (and many-to-one) cardinality

The one-to-many and many-to-one cardinality options are almost identical and are also the most commonly used.

When configuring a one-to-many or many-to-one relationship, select the option that corresponds to the order in which the columns were related. Think about how you would use themProduct IDColumn in each table to configure the relationship between theproductsmisaletables. That's whyProduct IDcolumn no.productsthe table contains unique values, the cardinality type would be too. The cardinality would be many to one if the tables were connected in opposite directions, i.e.sale of products.

One-to-one cardinality

Both columns in a 1:1 relationship have unique values. This type of cardinality is unusual and likely indicates suboptimal model design due to redundant data storage.

many-to-many cardinality

Both columns can have duplicate values ​​in a many-to-many relationship. This is a rare type of cardinality. This is often useful when creating complex model requirements. It can be used to connect many facts or facts with finer granularity. For example, if the sales target data is stored at the product category level and the product dimension table is stored at the product level.

Currently, models built for Power BI Report Server do not support the many-to-many cardinality type.

cross filter direction

Across filter directionis assigned to each of the relationships in the Power BI model. The directions in which the filters propagate are determined by your configuration. The cardinality type determines the available cross filter options.

The conditions"Simple Cross Filter Direction"mi"both directions cross filter"they are interchangeable. Bidirectional relationships are those that filter to both sides.

The direction of the cross filter for 1:n relationships is always"as"page with whichmany"the side is optional (bidirectional). In the case of one-to-one relationships, the direction of the cross filter always starts from both tables. Finally, for many-to-many relationships, the direction of the cross filter can come from one or both tables. If a cardinality type a"as"Page, filters always propagate from this page.

A new property is available when Cross Filter Direction is set to Both. When Power BI is appliedRow Level Security (RLS)rules, you can apply bidirectional filtering. A template calculation can be used to change the direction of the cross-relationship filter, including turning off filter propagation. EITHERCROSS FILTER DAXThe function is used for this.

Bidirectional relationships can negatively affect performance. Also, trying to establish a bi-directional relationship can lead to ambiguous filter propagation paths. In this case, Power BI Desktop might not be able to commit the relationship change and an error message is displayed. Power BI Desktop, on the other hand, occasionally allows you to define ambiguous relationship paths between tables.

The arrowheads along the relationship line in the model view in Power BI Desktop can be used to interpret the cross filter direction of a relationship. A single arrowhead indicates a one-way filter in the direction of the arrowhead, while a double arrowhead indicates a bidirectional relationship.

Activate that relationship

Only one active filter propagation path is allowed between two model tables. Additional relationship paths can be added, but they must be set to inactive. Inactive relationships can only be made active during the evaluation of a model calculation. EITHERDAX USER RELATIONSfunction is used to achieve this.

In general, it is recommended to define active relationships whenever possible. They expand the scope and potential use of your model by report authors. Roleplay dimension tables must be duplicated in your model if only active relationships are used.

However, under certain circumstances, you can define one or more inactive relationships for a role play dimension table. You should consider this design if:

• There is no need for report images to be filtered by multiple functions at the same time.
• oDAX USER RELATIONSThe function is used to activate a specific relationship for model calculations.

The active or inactive status of a relationship can be interpreted in the Power BI Desktop model view. TOsolid lineindicates an active relationship, while aBroken lineindicates an inactive relationship.

Assume referential integrity

Only one-to-many and one-to-one relationships between twoDirectQueryStorage mode tables that belong to the same source group are eligible for the Assume Referential Integrity property. only if he"many"The side column does not containzerosYou can enable this property.

When enabled, native queries sent to the data source use aINTERNAL CONNECTIONinstead of oneOUTER JOINto connect the two tables. Enabling this property generally improves query performance, but it depends on the data source specifications.

If there is a database foreign key constraint between two tables, this property should always be enabled. Consider enabling the property even when there is no foreign key constraint as long as data integrity is maintained.

If the data integrity of the tables is compromised, the inner join removes the rows that do not match. consider a modelsales desktogetherProduct IDMissing related column valueproductsTable. Unknown product sales lines are removed from propagation by filter.productstable for themsaleTable. This would overstate the sales figures.

Power BI model relationships - relevant DAX features

Several DAX features are useful for Power BI model relationships. Each function is briefly described in the following list:

• RELATED: Retrieves the value of a relation"as" page. This is useful when working with multi-table calculations that are evaluated row by row.
• RELATED TABLE: Retrieves a table of rows from the "many" side of the relationship.
• USER RELATIONSHIP- Uses a Power BI-specific inactive model relationship by default. This is useful when your model has a table of render measures and you want to create inactive relationships from it.
• CROSS FILTER:Filter propagation is disabled or the cross filter address relationship has changed (to one or both) (none). It's useful when you need to change or override Power BI model relationships when evaluating a calculation.
• CONNECTING VALUES: Concatenates two or more text strings into one. When tables in the same source group belong to the same source group, this function supports multi-column relationships inDirectQuerymodels.
• TREAT AS: Filter columns of an unlinked table using the results of a table expression. This is useful in advanced scenarios when you need to create a virtual relationship when evaluating a calculation.
• father and son functions: A collection of related functions for creating computed columns to naturalize a hierarchy of parent and child elements. These columns can be used to create a fixed level hierarchy.

Power BI model relationships - relationship score

Relationships in the Power BI model are classified as regular or finite in terms of scoring. It is not a relationship property that can be changed. In fact, the cardinality type and the data source of the two linked tables are derived from it. Understanding the nature of the assessment is critical, as data integrity can have an impact or consequence on performance.

To fully understand relationship evaluations, some modeling theory is first required.

All data for an import orDirectQuerymodel comes fromPara o VertiCache or source database. Power BI can detect the presence of a "as"side of a relationship in both cases.

Below is a composite model.

The composite model in this case consists of two source groups: onePara o Vertihome group and aDirectQuerysource group. there are three tablesPara o Vertihome group, and two are in theDirectQuerysource group. a table insidePara o VertiThe source group can be linked to a table inDirectQuerySource group using a cross-source group relationship.

regular relationships

If the query engine can identify the"as"Power BI model relationship page, considered normal. It has been confirmed that the"as"The side column has unique values. There are regular relationships in all one-to-many relationships within the source group.

The following example shows two regular relations, both labeled R. ORPara o VertiThe source group contains a one-to-many relationship, soDirectQueryThose.

Power BI creates a data structure for each regular relationship in the import models, where all the data is storedPara o VertiCache at the time of data update. Data structures are indexed column-by-column mappings of all values ​​and are intended to speed up table joins during query execution.

Regular relations allow table expansion during query execution. By adding the native columns of the base table and then extending them to related tables, table extension results in the creation of a virtual table. Table expansion occurs in the query engine for import tables and in the native query sent to the source database for DirectQuery tables (provided the Assume referential integrity property is not enabled). The query engine then operates on the expanded table, applying filters and grouping by column values ​​in the expanded table.

Even if the relation is not used in a calculation, inactive relations are expanded. Table expansion is not affected by bidirectional relationships.

table extension"many"For him "as"One-to-many relationship pages are achieved withLEFT OUTER LINKSemantics. An empty virtual line is added"as"Page Table if there is no corresponding value of the "many" page. Only regular relationships, not limited relationships, are subject to this behavior.

For one-to-one relationships within the source group, table expansion also occurs, but with FULLOUTER JOINSemantics. When this type of connection is used, virtual blank lines are added on both sides.

Virtual lines without members are virtually unheard of. Referential integrity violations occur when the"manySide value "one" does not match side value "one". In theory, such spaces should not exist. The source data can be deleted or repaired to delete it.

See how table expansion works with an animated example.

In this example, the model consists of three tables:Category, product and distribution.There is a one-to-many relationship between them.Categorymiproduct tables,and between theproductsmisaletables. Two rows form the category table, three rows form the tableproduct table,and five lines form thesales deskThere are no referential integrity violations since both sides of all relationships have matching values.

The result is an extended table of query times. Columns from all three tables are combined into this table. It is essentially a denormalized view of the information in the three tables. A new row is addedsaleTable with a production ID value of (9) that does not contain a matchproductsTable. It is a referential integrity violation. The Category and Product table columns in the expanded table have (empty) values ​​in the new row.

limited relationships

When there is no guarantee"as"On the other hand, Power BI model relationships are limited. There are two types of constrained relationships:

• Many-to-many cardinality is used in the relationship (even if one or both columns contain unique values).
• It is a cross-sourced relationship (which can only be the case with composite models).

The following example shows two bounded relations, both labeled L. InsidePara o VertiSource Group There is a many-to-many relationship and a one-to-many relationship between source groups.

Bounded relationships are never created in the import model data structures. Table links are resolved at query time, in this case by Power BI.

In the case of limited relationships, no table expansion occurs. Virtual blank rows are not added to compensate for referential integrity violations, since table joins are used.INTERNAL CONNECTIONSemantics.

Other limitations that come with linked relationships are:

• o"as"Side column values ​​cannot be retrieved withRELATIVE DAXFunction.
• The execution topologyRLSis limited

It's not always easy to see if Power BI model relationships are regular or constrained in the Power BI Desktop model view. A many-to-many relationship, like a one-to-many relationship when it comes to a cross-origin group relationship, will always be limited. To find out if this is a cross-source group relationship, look at the table storage modes and data sources.

Power BI model relationships - precedence rules

Multiple and therefore ambiguous filter propagation paths can be introduced between model tables via bidirectional relationships. The following are the ambiguity detection and path resolution precedence rules used by Power BI:

• Many-to-one, one-to-one, and limited relationships
• many-to-many relationships
• In the opposite direction bidirectional relationships (i.e., on the "many" side)

Power BI model relationships: Performance preference

The following list ranks the filter propagation performance from fastest to slowest:

• One-to-many source relationships within the group
• Many-to-many model relationships in Power BI are achieved with an intermediate table and include at least one bi-directional relationship
• Many-to-many cardinality relationships
• Relations between groups of parents

Diploma

This blog describes the Power BI model relationships in detail. In addition, it also provides a brief introduction to Power BI.

Visit our website to explore Hevo

PowerBIIt is a great running tool.data analysismidisplayfor your company data. However, sometimes you need to pull this data from multiple sources into your PowerBI account for analysis. Developing an in-house solution for this process can be a time-consuming and expensive endeavor.cube hevo, however offersData pipeline without codethat can automate your data transfer process so you can focus on other aspects of your business like analytics, customer management, etc.

This platform allows you to transfer data frommore than 100 fontsfor BI tools likePowerBI,mi Cloud-based data warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. They will give you a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin?Recordfor a free 14-day trial and experience the feature-rich Hevo suite first-hand. You can also take a look at the unbeatablePricesThis will help you choose the right plan for your business needs.