cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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. Smiley Happy

Column3 =
CALCULATE (
    FIRSTNONBLANK ( Table2[Column4], 1 ),
    FILTER (
        Table2,
        Table2[Column1] = Table1[Column1]
            && ( Table1[Column2] >= Table2[Column2]
            && Table1[Column2] <= Table2[Column3] )
    )
)

c1.PNG

 

Regards

View solution in original post

11 REPLIES 11
Microsoft
Microsoft

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. Smiley Happy

Column3 =
CALCULATE (
    FIRSTNONBLANK ( Table2[Column4], 1 ),
    FILTER (
        Table2,
        Table2[Column1] = Table1[Column1]
            && ( Table1[Column2] >= Table2[Column2]
            && Table1[Column2] <= Table2[Column3] )
    )
)

c1.PNG

 

Regards

View solution in original post

This works perfectly.

 

Thank you to every one for your time it is very much apritiated

Alan

Resolver I
Resolver 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}

Frequent Visitor

This would have described my question a bit better

Table 1

Column1Column2
192.168.123

Table 2

Column1Column2Column3Column4
192.168.100150location1
192.168.151200location2
192.168.201250location3

 

The result should be

Table 1

Column1Column2Column3
192.168.123location1
192.168.224location3

 

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

 

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 get

Select * 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 get

select * 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&nbsp;more to type query&nbsp;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 to

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



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&nbsp;beginners understanding way.

(EDIT: put the codes in code-blocks

Frequent Visitor

Sorry 😞

 

(EDIT: put the codes in code-blocks?

 

I tried to ut it in a new table, not sure if thats what im supposed to do. Table 2 = 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

 

It didnt work. red wiggly underscores prety much all the way 😞

 

Sorry to be a pain in the a......

I'm assuming you have a connection to a database where you're retrieving the information from?

If so, edit your Query (or create a new one).

 

This creates a new table with the three fields (col1, col2, col4)

 

step 1

query.JPG

 

 

 

step 2

query2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

step 3

query3.JPG

 

 

Frequent Visitor

Sorry no,

The sources are 2 separate spreadsheets that are made available to me via sharepoint.

Anonymous
Not applicable

Hi

 

 

 

 You can join those tables in relationship model with matching column and in the report, you can drag and drop whatever the columns you want from both the tables.

Reporu1.png

Report1.png

 

 

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 Smiley Sad

Anonymous
Not applicable

Hi

 

  Another quick approach is, you can duplicate the table and can keep only column1 and column 4 (location) then you can remove the duplicates and consider that table (query) for your relationship model.

 

 Thanks

 Hari

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors