Help needed on Lookup value function

[koushikvps]

Hello Experts,

Please see the below table. i am trying to acheive the Parent column in Dax. Trying to lookup a value within the table. Also handle NULL's in the column C as well. in Excel, i can do a vlookp and derive the value, but, look up value function is not working as i there are duplicates in Column C. So not sure, what to do. need help.

A	B	C	Parent - it’s a Dax driven Column
1	Apple	5	Fruits
2	Orange	5	Fruits
3	Banana	5	Fruits
4	Computer
5	Fruits	5	Fruits
6	Tomato	10	Vegetables
7	Onion	10	Vegetables
8	Cabbage	10	Vegetables
9	Brocolli	10	Vegetables
10	Vegetables	10	Vegetables

[v-danhe-msft · Accepted solution]

Hi @koushikvps,

Based on my test, you could refer to below steps:

Sample data:

Create a calculated column:

Parent = LOOKUPVALUE (Table1[B],Table1[A], 'Table1'[C])

Now you could see the result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ap40ktstz0tl4tn/Help%20needed%20on%20Lookup%20value%20function.pbix?dl=0

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @koushikvps,

Based on my test, you could refer to below steps:

Sample data:

Create a calculated column:

Parent = LOOKUPVALUE (Table1[B],Table1[A], 'Table1'[C])

Now you could see the result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ap40ktstz0tl4tn/Help%20needed%20on%20Lookup%20value%20function.pbix?dl=0

Regards,

Daniel He

[koushikvps · Accepted solution]

Hi Daniel,

That worked. Thank you so much for the help.

[Anonymous]

@koushikvps you could use the related function. Also if a relationhip exists on the column b in your fact and dimension you should be able to just drag and drop the field. Could you send a screenshot of your model?

https://msdn.microsoft.com/en-us/query-bi/dax/related-function-dax

[koushikvps]

Related Function doesn't help as i am doing a lookup within the same table. 

[Anonymous]

you could create a calculated table with distinct values linking fruit name and type(parent column) and then join it and use related or drag and drop value from that table