Forum Discussion
How to join two tables with multiple common fields but no unique identifier
Hi, I'm still quite new to Power BI especially with the modeling part.
I'm trying to join a table called LeadTimeGrid with the following columns:
| CUSTOMER | REGION | FACTORY | STANDARD LEAD TIME |
| Apple | Asia | A | 10 |
| Apple | Africa | B | 8 |
| Apple | Europe | C | 8 |
| Boy | Europe | C | 5 |
| Boy | Asia | A | 7 |
| Boy | North America | D | 7 |
| Cat | North America | E | 3 |
with the main FactTable:
| CUSTOMER | REGION | COUNTRY | FACTORY | ACTUAL LEAD TIME |
| Apple | Asia | China | A | 15 |
| Cat | North America | USA | E | 25 |
| Boy | Europe | France | C | 30 |
| Apple | Europe | Italy | C | 2 |
Anyway I can create a new table or visualization that has the following data fields:
| CUSTOMER | REGION | COUNTRY | FACTORY | ACTUAL LEAD TIME | STANDARD LEAD TIME |
| Apple | Asia | China | A | 15 | 10 |
| Cat | North America | USA | E | 25 | 3 |
| Boy | Europe | France | C | 30 | 5 |
| Apple | Europe | Italy | C | 2 | 8 |
None of the values are unique, and I'm not sure if I'm supposed to create a many-to-many relationship with the different fields.
Any help?
Anonymous
you can just create a column
Column = maxx(FILTER(LeadTimeGrid,'Fact'[CUSTOMER]=LeadTimeGrid[CUSTOMER]&&'Fact'[REGION]=LeadTimeGrid[REGION]&&'Fact'[FACTORY]=LeadTimeGrid[FACTORY]),LeadTimeGrid[STANDARD LEAD TIME])
3 Replies
Anonymous
you can just create a column
Column = maxx(FILTER(LeadTimeGrid,'Fact'[CUSTOMER]=LeadTimeGrid[CUSTOMER]&&'Fact'[REGION]=LeadTimeGrid[REGION]&&'Fact'[FACTORY]=LeadTimeGrid[FACTORY]),LeadTimeGrid[STANDARD LEAD TIME])you are welcome