Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Baldy
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
v-ljerr-msft
Employee
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. 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
v-ljerr-msft
Employee
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. 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

This works perfectly.

 

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

Alan

WillemC
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}

Baldy
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

Baldy
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

 

 

Baldy
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.