Comparing data from one table to the other and adding column

[Prashiyer]

I am working on structuring a db and using power BI to generate visuals. I have two tables where I need to add a new column based on the comparison of one to the other. This below is the Department table

I want to add a new column to my main table by comparing the Dept_Name from the above table to my main table and displaying the operation_area.

I tried running this code

Operation_Area = ADDCOLUMNS('IN_Data', "Operation_Area",  if('In_data'[Dept_Name]=Dept_table[Dept_name], Operational_Area, "Missing)

I am sure this is a wrong code because I am asking to compare but this code does seem to have a proper functioning instruction like Sum or Aggregate. I want to do what Vlookup would do in excel.

[Prashiyer]

@burakkaragoz Thank you very much. I think there was some other issue with the department name being wrong. The RELATED function had done its job but I missed the fact that the top rows were empty as the dept_name in the Dept table did not match the name in the main table. 

Thanks again for your help.

[burakkaragoz · Accepted solution]

Hi @Prashiyer ,

You’re on the right track! What you want is basically a VLOOKUP-like operation in Power BI/DAX to pull the Operation_Area from Dept_table into your IN_Data table based on matching Dept_Name.

In DAX, the most straightforward way is to use the RELATED function, but for that, you first need to set up a relationship between your two tables (IN_Data[Dept_Name] to Dept_table[Dept_name]) in the Power BI model. Once that’s ready, you can add a calculated column to IN_Data like:

Operation_Area = RELATED(Dept_table[Operational_Area])

If you can’t (or don’t want to) use relationships, you can use LOOKUPVALUE like this:

Operation_Area = LOOKUPVALUE( Dept_table[Operational_Area], Dept_table[Dept_name], IN_Data[Dept_Name] )

This will pull the right Operation_Area for each Dept_Name in your main table, just like Excel’s VLOOKUP.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[Prashiyer]

I have the tables related. The department Table is linked to my main table with a 1 to many cardinality. Yet, when I did run the RELATED code earlier as well, I got no error but my cells are empty. I feel this is not comparing and retrieving data as it should.

This is my relationship diagram