Forum Discussion
Multiple relations
How to create multiple column relations between two tables.
For Ex:
I have two tables:
1. Product Group table
| Company | Product Group | Product |
| A | PG1 | ITM1 |
| A | PG1 | ITM2 |
| B | PG2 | ITM1 |
2. Sales Table
| Company | Product | Sales Amount |
| A | ITM1 | 100 |
| A | ITM2 | 200 |
| B | ITM1 | 300 |
Requirement is to fetch the Product group from table 1 based upon company and product values from table 2.
In Power BI's model, a relationship can be created between two tables only by connecting a single column from each one.
Therefore, what you should do is add a key column to each of the tables that concatenates the Company and Product columns values for each row. You can do this either via a DAX expression or in Power Query.
For example, you can create a calculated column in each table using the following DAX expressions-
In the Product Group table-
Key = 'Product Group'[Company] & "|" & 'Product Group'[Product]
In the Sales table-
ProductGroupFK = 'Sales'[Company] & "|" & 'Sales'[Product]
And then create a relationship between them.
1 Reply
In Power BI's model, a relationship can be created between two tables only by connecting a single column from each one.
Therefore, what you should do is add a key column to each of the tables that concatenates the Company and Product columns values for each row. You can do this either via a DAX expression or in Power Query.
For example, you can create a calculated column in each table using the following DAX expressions-
In the Product Group table-
Key = 'Product Group'[Company] & "|" & 'Product Group'[Product]
In the Sales table-
ProductGroupFK = 'Sales'[Company] & "|" & 'Sales'[Product]
And then create a relationship between them.