cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gskoglund Frequent Visitor
Frequent Visitor

Is it VLOOKUP or something else

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Is it VLOOKUP or something else

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

6 REPLIES 6
Phil_Seamark Super Contributor
Super Contributor

Re: Is it VLOOKUP or something else

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?  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
gskoglund Frequent Visitor
Frequent Visitor

Re: Is it VLOOKUP or something else

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. Smiley Wink I've gone down a few different avenues and I guess just a little confused. 

Phil_Seamark Super Contributor
Super Contributor

Re: Is it VLOOKUP or something else

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?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

gskoglund Frequent Visitor
Frequent Visitor

Re: Is it VLOOKUP or something else

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 Super Contributor
Super Contributor

Re: Is it VLOOKUP or something else

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

gskoglund Frequent Visitor
Frequent Visitor

Re: Is it VLOOKUP or something else

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!