Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michellepace
Resolver III
Resolver III

Create a column based on a lookup in another table

Hi, I've spent the last hour trying to understand this and google around. I must be missing something completely because it simply cannot be that difficult. Alright, I have the below two tables:

 

___________________

Table 1: 

AccNumber |

 

Table 2:

AccNumber | AccDescrip

___________________

 

All I want to do is create an additional calculated column called "Main" in Table 1. Where for each row, the equivalent vlookup function would be: 

 

Main = vlookup( Table1.AccNumber * 1000,  Table2,  2,  False)

 

Could anyone please shed some light as to what my formula would be for creating a calculated column?

 

2 ACCEPTED SOLUTIONS

Hi michellepace,

You could try below

Column = LOOKUPVALUE('Table2'[AccNumber],'Table2'[AccNumber],'Table 1'[AccNumber])*1000

333.PNG334.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi, it's amazing how I can make something so simple so complicated :). The RELATED function worked for me. Just posting this pic here for anyone else who may find it useful. 

 

RELATED.png

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hi,

Given that there is relationship between 2 tables, you can use RELATED function in DAX to achieve this:

Use the below code to create the column in Table 2.

Main = RELATED( Table1[AccNumber] )

Thanks.

Hi @Anonymous , thank you for your reply. I've tried to follow your advice but obviously I'm getting something wrong... can anyone please tell me why I am getting this error? As far as I can see my syntax is correct...?

 

1.png2.png

Hi michellepace,

You could try below

Column = LOOKUPVALUE('Table2'[AccNumber],'Table2'[AccNumber],'Table 1'[AccNumber])*1000

333.PNG334.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, it's amazing how I can make something so simple so complicated :). The RELATED function worked for me. Just posting this pic here for anyone else who may find it useful. 

 

RELATED.png

Thank you! So helpful!!

Hi,

That formula has to be written in the AccBalances Table and it should be a calculated column formula (not a measure)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, thanks again for your reply. What I am trying to do is get a column which is in the AccBalances Table, into my Hierarchy table. So... I'm not really sure why I would then be writing the formula in the table (AccBalances) which already has the column I need. Sorry, I am completely confused here. Can you please detail exactly what you mean?

 

And in fact, although I may be wrong, I found that the Lookup function only works when I am on the 'many' side of the cardinality looking up into a table on the '1 side' of the cardinality. That is, there is no ambiguity for the lookup function. This makes sense to me. Am I correct or imagining it?

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.