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!

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors