Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |