cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gskoglund
New Member

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

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!

View solution in original post

6 REPLIES 6
Phil_Seamark
Microsoft
Microsoft

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!

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. 

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!

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.

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!

View solution in original post

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors