CODEWITHSUNDEEP
lookuppowerbidax
Sharani Rajasekaran
Sharani Rajasekaran

Reputation: 25

How to lookup the values of a column in a table, if one of its column matches with the other table

I'm using Power BI desktop. I have 2 tables named as Company details and Tax. These tables contains a common column Company ID. Actually these 2 tables contain different columns but I have only shown the columns needed for this question.

Company details:

+-------------+--------------+
| Company UID | Company name |  
+-------------+--------------+
| C1000       | A            |  
| C1400       | B            |  
| C1200       | C            |  
| C1100       | D            |  
| C1300       | E            |  
+-------------+--------------+

Tax:

+-------------+-----+
| Company UID | FID |  
+-------------+-----+
|       C1000 | F22 |  
|       C1100 | F20 |  
|       C1200 | F22 |  
|       C1300 | F21 |  
|       C1400 | F20 |  
+-------------+-----+

Now I need to create a calculated column Fcode in Company details which has the values returned by the Existing column FID in TAX if the values of the column Company UID in both the table matches.

Expected result

Company details:

+-------------+--------------+-----+
| Company UID | Company name | FID |  
+-------------+--------------+-----+
|       C1000 | A            | F22 |  
|       C1400 | B            | F20 |  
|       C1200 | C            | F22 |  
|       C1100 | C            | F20 |  
|       C1300 | D            | F21 |  
+-------------+--------------+-----+

My Dax:

    Fcode = IF(VALUES('Company details'[Company UID])=VALUES('Tax'[Company UID]),LOOKUPVALUE('Tax'[FID]), 0))

I'm stuck up with what dax should I give in the place of TrueResult inside the If Loop. so I just tried on giving lookup function. Ofcourse it shows an error. Can anyone suggest what I should replace on behalf of lookup or any other way to get the expected result.

Upvotes: 1

Views: 5616

Answers (2)

nothingman
nothingman

Reputation: 144

You can go to "Edit Queries" window. Merge the two tables using inner join and CompanyUID and then strip the results to leave only FID from the second table. https://www.youtube.com/watch?v=hVWSxX-uF-0

Upvotes: 1

nothingman
nothingman

Reputation: 144

First of all, why don't you add relationship between those columns and use RELATED function to get the related FID value from Tax. If you don't want to add relationship then you can still use CROSSJOIN. Take a look at this article https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Upvotes: 0

Related Questions