Forum Discussion
Joining tables with conditional statement using min and max
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 |
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
2 Replies
- Jihwan_Kim
Super User
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