Can you have more than one functional relationship between tables

Question

Can you have more than one functional relationship between two tables in a Power Pivot data model

Emily Foster · Answer

Absolutely! In Power Pivot, it is indeed possible to have more than one functional relationship between two tables within a data model. This feature is particularly useful when dealing with complex data relationships where multiple connections need to be established between two tables. Having multiple relationships between two tables allows you to create additional paths for data to flow and be analyzed within your model. This can be especially beneficial when you have different types of connections or relationships that you need to consider separately. For example, let's say you have a Sales table and a Date table. You may want to establish one relationship between the Sales table and the Date table based on the order date and another relationship based on the ship date. This way, you can analyze and view your sales data from different perspectives depending on the relationship being utilized. To implement multiple relationships between two tables in Power Pivot, you can use a technique called "bi-directional filtering" or create calculated relationships based on specific conditions or criteria. Here's a simplified example using DAX (Data Analysis Expressions) code to demonstrate creating multiple relationships between two tables: EVALUATE SUMMARIZECOLUMNS ( 'Date'[Year], "Total Sales Based on Order Date", CALCULATE(SUM('Sales'[Amount]), USERELATIONSHIP('Sales'[Order Date], 'Date'[Date])), "Total Sales Based on Ship Date", CALCULATE(SUM('Sales'[Amount]), USERELATIONSHIP('Sales'[Ship Date], 'Date'[Date])) ) This code snippet showcases how you can calculate the total sales based on different date relationships within your Power Pivot model. By leveraging multiple functional relationships between tables in Power Pivot, you can enhance the depth and flexibility of your data analysis, providing a more comprehensive view of your data across various dimensions.