Forum Discussion
How to use an if statement to create a column based on values from 2 tables
Hi,
I am new Power BI and am really struggling to create a column
I have 2 tables, 1 that contains stripped down IP address columns and another table that contains an IP range and a location name.
Table 1 = column1 (192.168) column2 (123)
Table 2 = colunm1 (192.168) column2 (100) column3 (150) column4 (location name)
I need to pull the location name into table1
In its simplest form I believe I can express the query as follows:
if table1 column 1 = table2 column 1 and table1 column2 >= table2 column2 and <= table2 column3 return table2 column4
Any help or insight would be greatly appreciated
Alan
Hi Baldy,
Based on my test with your sample data, you should be able to use the formula(DAX) below to create new calculate column in Table1 in your scenario. :smileyhappy:
Column3 = CALCULATE ( FIRSTNONBLANK ( Table2[Column4], 1 ), FILTER ( Table2, Table2[Column1] = Table1[Column1] && ( Table1[Column2] >= Table2[Column2] && Table1[Column2] <= Table2[Column3] ) ) )Regards
11 Replies
v-ljerr-msftMicrosoft Employee
Hi Baldy,
Based on my test with your sample data, you should be able to use the formula(DAX) below to create new calculate column in Table1 in your scenario. :smileyhappy:
Column3 = CALCULATE ( FIRSTNONBLANK ( Table2[Column4], 1 ), FILTER ( Table2, Table2[Column1] = Table1[Column1] && ( Table1[Column2] >= Table2[Column2] && Table1[Column2] <= Table2[Column3] ) ) )Regards
This works perfectly.
Thank you to every one for your time it is very much apritiated
Alan
Thank you for the quick reply.
I cannont use relationships because there are many values that are the same.
We operate a very large class B network. 192.168 appears many times in column1 on both sheets :smileysad:
WillemCResolver I
Unless I don't understand it, use your query for this.
select t1.*, t2.* from table1 t1 left join table2 t2 on t2.col1=t1.col1 and t1.col2>=t2.col2 and t1.col2<=t2.col3
Then your joining table2 on table1, with the criteria that col1 of both are equal, and col2 of table1 is equal or greater then table2.col2 and equal or smaller then table2.col3.
The select part you can alter to prevent extra data in for example,
select t1.col1, t1.col2, t2.col4 from table1
This wil result in
192.168 ; 126 ; {location}
This would have described my question a bit better
Table 1
Column1 Column2 192.168. 123 Table 2
Column1 Column2 Column3 Column4 192.168. 100 150 location1 192.168. 151 200 location2 192.168. 201 250 location3 The result should be
Table 1
Column1 Column2 Column3 192.168. 123 location1 192.168. 224 location3 From what little I know about sql I believe your query is correct
However I dont understand t1.*, t2.* from table1 t1. I get the * means "all" but the t1,t2 I dont understand where this comes from or where to run your whery.
Sorry for being such a noob :(
I also need the data pulled into in the original table as a new column
Alan
- WillemC
No problem.
I'll type it with some more additional info.select t1.*, t2.* from table1 t1 left join table2 t2 on t2.col1=t1.col1 and t1.col2>=t2.col2 and t1.col2<=t2.col3
As a small explanation:select * from table1
that's your starting base.
When you're going to join tables, you have to specify which columns from which tables your referring to.
To make your typwork a bit smaller you can alter this to
select * from table1 as t1(you can make it even smaller with ditching the 'as')
so you would getSelect * FROM table1 t1
now you're going to join the second table with
left join table2 t2
Here again, i'm adding the t2 to prevent me to have to type the table2 all the time.
so you would getselect * from table1 t1 Left join table2 t2
To know on what to join, jou add the columns which correspondent to each other.
left join table2 t2 on t1.col1=t2col1 and t1.col>=t2.col2 and t1.col2<=t2.col3
here the t1 and t2 come in handy, if you don't want to use it you would get this more to type query which does exactly the same
left join table2 on table1.col1=table2.col1 AND table1.col2>=table2.col2 .....
Then you can select what values you want to show by altering the select * from statement toSELECT t1.col1, t1.col2, t2.col4 FROM Table1 t1 LEFT JOIN table2 t2 on t1.col1=t2col1 AND t1.col2>=t2.col2 AND t1.col2<=t2.col3
if you use t1.*,t2.* all values are taken so you would get the same as when in the above query you type
SELECT t1.col1, t1.col2,t2.col1,t2.col2,t2.col3,t2.col4 From Table1
so your query should be:SELECT t1.col1, t1.col2, t2.col4 FROM Table1 t1 LEFT JOIN table2 t2 ON t1.col1=t2col1 AND t1.col2>=t2.col2 AND t1.col2<=t2.col3
This query creates a table in Power BI generates a table which contains your data.
Hopefully I made it more clear this way in a for beginners understanding way.(EDIT: put the codes in code-blocks
