Is it VLOOKUP or something else

[gskoglund]

Hi............I'm working with our GL history table and trying to match it up with the GP table that has account name & #. I built the relationship between the ACTINDX field. I used merge tables and it did not return the result I expected. I want to bring back the ACTNUMST field into the other table so I can categorize by sales, COGS, etc. What am I missing? FYI - new to PowerBI.

[Phil_Seamark]

Hi @gskoglund

You can possibly use VLOOKUP but it might not be the most efficient approach.  When you say you created a relationship, do you mean in DAX?  

[gskoglund]

Thanks for responding Phil. No through the relationship builder. From my understanding and again newbie here, if you created a relationship between the 2 tables with the same field that it would merge. I would like the most efficient approach. 😉 I've gone down a few different avenues and I guess just a little confused. 

[Phil_Seamark]

They won't merge the tables as such, but you can write formulas using columns from both tables.

Are you trying to create a calcuated column?  If so, in which table?

[gskoglund]

Maybe I'm misunderstanding what a calculated column is. In my GL history table I have the ACTINDX field which represents an account #. I have the account # in the GL00105 table, which also references the ACTINDX field. So I would like to get the ACTNUMST fromt he GL00105 table over to the other table so I can see everything by acct # vs. an index #. Normally I would just do this through a vlookup in Excel.

[Phil_Seamark · Accepted solution]

ok, so if I understand properly, you are keen to have a column/field in your [GL History] table use a value from the [GL00105] table and you have a relationship.  

Try this and let me know how you get on. 

Add a calculated column to your [GL History] table, something like this

Test Column = RELATED(GL00105[ACTNUMST])

If that works the way you expect you can incorporate that logic in formulas that include other columns in your [GL History] table.

[gskoglund]

Thanks Phil! That is what I was looking for. I saw that Related before but there was a disconnect for some reason. Thanks for your help!