Forum Discussion

mukeshmutreja's avatar
mukeshmutreja
Microsoft Employee
10 years ago
Solved

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 ...
  • itayrom's avatar
    itayrom
    10 years ago

    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.

itayrom's avatar
itayrom
Resolver II
10 years ago

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.