Multiple relations

[mukeshmutreja]

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.

[itayrom · Accepted solution]

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.