Joining tables with conditional statement using min and max

[Anonymous]

Hi, 

I have two tables. I would like to bring the column [Category] from table2, into table1. For my relationship I.D., I believe that I should use table1[Number] joined with a conditional statement for table2[Start Number] & table2[End Number]. 

I have provided an example below with table1 & table2. Table1[Goal Column] is what I am trying to do. As you can see, it's a one-to-many relationship, from table2 to table1, exept table2 must use two columns for the relationship I.D.: [Start Number] and [End Number]. The table2[Start number] is the minimum value and table2[End Number] is the maximum number. Any table1[Number] value between these values should have the appropriate table2[Category]. 

I appreciate your willingness to help me out! 

Table 1
Well	Number	Goal Column
justin	1000	Happy
justin	1001	Happy
justin	1002	Happy
justin	1003	Happy
justin	1004	Happy
justin	1005	Sad
justin	1006	Sad
justin	1007	Sad
brett	5000	Happy
brett	5001	Happy
brett	5002	Sad
brett	5003	Sad
brett	5004	Sad
brett	5005	Sad
brett	5006	Sad

Table 2
Well	Start Number	End Number	Category
justin	1000	1005	Happy
justin	1005	1007	Sad
brett	5000	5002	Happy
brett	5002	5006	Sad

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the link down below.

It is for creating a new column.

Goal Column CC =
VAR currentwell = Table1[Well]
RETURN
CALCULATE (
MAX ( Table2[Category] ),
FILTER (
Table2,
Table2[Well] = currentwell
&& Table1[Number] >= Table2[Start Number]
&& Table1[Number] <= Table2[End Number]
)
)

https://www.dropbox.com/s/fnj9itg4kd6z457/jklib.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Really appreciate the quick and correct response. I didn't know that you can create columns that import data from other views without a live table-to-table relationship.

Thanks again!