Creating a lookup table, but one problem...

[cole_lehmkuhler]

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!

[chrisu · Accepted solution]

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:

[cole_lehmkuhler]

Thank you!!!!! That worked!

[sornavoor]

What are the columns in the other table? And, what are you looking up one/ trying to join with? 

[cole_lehmkuhler]

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.