Forum Discussion
Comparing data from one table to the other and adding column
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.
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
3 Replies
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 AII 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
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.
