Forum Discussion
Creating a lookup table, but one problem...
Here's my lookup table so far
Start End Division
0000 9999 Gas
10000 19999 Diesel
20000 29999 Marine
30000 39999 Gas
40000 49999 Trans
So obviously I have gas twice and can't make a relationship because the other table I'm making a relationship with has repeats of gas diesel marine all of those. I need to know how to fix this so I can do both ranges for gas so when it is looked up I still get Gas, but I need to make a relationship between these two tables as well. Help!
One way to handle this many-to-many problem is to create a "bridge" table that contains a unique list of divisions. Your tables can then be connected by many-to-one relationships. If you set the relationship to be bi-directional, then filters should flow from the ranges table through the division table and into the sales table, effectively creating a many-to-many relationship.
The model would look something like this:
4 Replies
What are the columns in the other table? And, what are you looking up one/ trying to join with?
Its going to join with another table that has 100s of thousands of rows that are sales. These sales are labeled into divisions as well and contain gas and the other field values as well, but as it has so many rows there are thousands and thousands of repeats as well in. I would have no problem doing this except for that the in the table I posted there are two ranges for the "gas" value. Sorry if this is hard to understand I'm very very new to Power BI.
One way to handle this many-to-many problem is to create a "bridge" table that contains a unique list of divisions. Your tables can then be connected by many-to-one relationships. If you set the relationship to be bi-directional, then filters should flow from the ranges table through the division table and into the sales table, effectively creating a many-to-many relationship.
The model would look something like this:
Thank you!!!!! That worked!
