Power BI: Calculation from two tables
Maximum analysis performance: Power BI and the art of table relationships
In the world of data analysis, Power BI can be an important tool that enables complex data relationships to be visualized and analysed. A key aspect of this is the ability to perform calculations across multiple tables. In this blog post, we dive into the basics of table relationships in Power BI, explain the use of RELATED and RELATEDTABLE for data queries and provide practical examples and best practices for efficient data models.
Basics of table relationships in Power BI
Table relationships in Power BI are more than just connections between data points - they are the foundation on which meaningful and interactive data models are built. These relationships allow analysts to integrate, analyze and visualize complex data from disparate sources. To fully understand the meaning and functionality of these relationships, it is important to know their fundamentals and types.
Primary key and foreign key: The heart of the relationships
The primary key is a unique identifier in a table. Each data record in this table can be uniquely identified via its primary key. An illustrative example of this is a customer ID in a customer table, where each ID is assigned to a specific customer.
The foreign key refers to the primary key in another table, thus serving as a link between the tables. For example, an order table could contain a customer ID as a foreign key that refers to the primary key in the customer table.
Different types of relationships
Relationships between data are at the heart of many BI tools. These relationships allow us to link complex data from different sources and gain meaningful insights into our business processes. In Power BI, relationship types play a crucial role as they determine how data from different tables interacts and is interpreted. Below we will take a closer look at the different relationship types in Power BI and how they affect data analysis and reporting.
In Power BI, a distinction is made between four different relationship types, which define how the tables relate to each other.
One-to-one (1:1) means that each data record in the first table corresponds to exactly one data record in the second table. This relationship type is rare as it requires both tables to contain unique data records.
One-to-Many (1:N), on the other hand, is the most common type of relationship. Here, one data record in the first table (with the primary key) corresponds to several data records in the second table. An example would be a relationship between a table with product information (each product is unique) and a sales table in which each product can be sold several times. (See figure 1)
Many-to-one (N:1) is basically the reverse perspective of a 1:N relationship. Here, several data records in the first table correspond to one data record in the second table.
Many-to-Many (M:N) represents a relationship in which several rows from the first table belong to several rows from the second table. This type occurs, for example, when several products can be obtained from several suppliers and each supplier in turn supplies several products. This relationship can lead to challenges and errors in calculations in data models. It is therefore recommended to avoid this relationship wherever possible.
Use of RELATED and RELATEDTABLE
The RELATED and RELATEDTABLE functions in Power BI are central tools for working with relationship-oriented data models. They make it possible to efficiently query and analyze data from different but related tables. In order to fully understand their application and meaning, it is helpful to take a closer look at each function and its possible uses.
RELATED: Access to linked data
The RELATED function is used in Power BI to access values from another table that is linked via a relationship. It plays a crucial role in data modeling and analysis, especially in "one-to-many" and "many-to-one" relationships.
Application example: Assume you have a table with sales data (sales table) and a separate table with customer information (DIM_Customers). Each sale is assigned to an employee. To display the name of the employee in the sales table, you can use the RELATED function to retrieve the name from the employee table based on the employee ID. In DAX (Data Analysis Expressions) the formula could look like this: CustomerName = RELATED(DIM_Customers[FirstName and LastName]). This formula would be used in the sales table to extract the employee's name from the employee table.
RELATEDTABLE: Summary of data from linked tables
RELATEDTABLE is a powerful function that returns a table of values from a related table. It is often used in "one-to-many" relationships to calculate summarized data such as totals, averages or quantities.
Application example: Imagine you have a table with product information (product table) and a table with sales data (FCT_sales_data), where each product can be sold several times. To calculate the total sales per product, you can use the RELATEDTABLE function to summarize all sales of a product. A typical DAX formula could look like this: Total sales = SUMX(RELATEDTABLE(FCT_sales_data), FCT_sales_data[sales]). This formula would be used in the product table to calculate the total sales of each product, based on the data in the sales table.
In order to use these functions effectively, it is crucial that the relationships between the tables are defined correctly. Incorrectly defined relationships can lead to incorrect results. It should also be noted that RELATED can only be used in calculated columns or measure formulas, while RELATEDTABLE is typically used in measure formulas.
Examples of common scenarios for the calculation from two tables in Power BI
The use of RELATED and RELATEDTABLE in Power BI opens up a wide range of possibilities for detailed and meaningful data analyses. A particularly illustrative example is the calculation of turnover across several tables. To illustrate the use of these functions in practice, we will look at an extended scenario with sales data and product information.
Scenario: Sales analysis with sales and product data
Imagine you have two main tables in your Power BI model.
- Sales table: This table contains detailed sales data, including transaction IDs, sales data, product IDs and sales values.
Example sales table:
- Product table: This table lists all products with product IDs, product descriptions, categories and prices.
Example product table:
The aim is to create a comprehensive analysis of sales that takes into account not only sales figures but also product information.
Application of RELATED: Detailed product information in sales reports
- Problem definition: In the sales table you have the product ID, but no further information about the product.
- Solution: You can use the RELATED function to get product details such as the name, category or price from the product table into the sales table.
- Example: Product name = RELATED(product table[product name])
-
This formula is used in the sales table to retrieve the name of the product based on the product ID. The result in your sales report would look like this:
By using the RELATED function, the product information from the product table is inserted into the sales table, which makes the sales reports more informative.
Application of RELATEDTABLE: Aggregation of total sales per product
- Problem: You want to determine the total sales per product or category, but the sales table only contains individual transactions.
- Solution: With RELATEDTABLE you can summarize all relevant sales data for each product to calculate total sales.
- Example: To calculate the total sales per product, you can use the RELATEDTABLE function. Here is an example formula in Power BI: Total salesProduct = SUMX(RELATEDTABLE(Sales table), Sales table[Sales])
This formula in the product table calculates the total sales of each product by summarizing all sales from the sales table. The result in your product table would look like this:
By using the RELATEDTABLE function, all sales data from the sales table is summarized to calculate the total sales per product. This enables a detailed analysis of sales for each product.
Best practices for efficient data models in Power BI
Efficient data models are at the heart of powerful analyses in Power BI. They determine not only the accuracy and relevance of the analysis results, but also the speed and efficiency with which these results are achieved. Here are some advanced best practices for creating and maintaining efficient and powerful data models in Power BI.
A clear and logical structure of the relationships between the tables is crucial for the integrity and performance of the data model. It is important to precisely identify primary and foreign key relationships and avoid redundant or circular relationships.
Another important approach is the use of the star schema. This data modeling principle, in which a central fact table is surrounded by several dimension tables, makes it easier to query and analyze data. It offers an intuitive and clear structure and improves performance as fewer links are required. The organization of the data, where transaction data is stored in the fact table and descriptive attributes in the dimension tables, is essential.
Optimizing data through data cleansing also helps to increase efficiency. Simpler and less complex DAX formulas are often more efficient and easier to maintain. Consideration should also be given to how often data needs to be updated, as frequent updates, especially with large amounts of data, can affect performance. Regular checking and maintenance of the data model is also essential. Bottlenecks can be identified through performance monitoring, for example with the Performance Analyzer in Power BI. If the business requirement changes, the model should also be adapted.
An efficient data model in Power BI is the result of careful planning, clever structuring and regular maintenance.
.webp)
