Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |