Cross table reference problem

[Daz4]

I have two tables joined with a one to many ID. 

I need to find the related value for when column X = y.

Would anyone be able to assist with the column creation formula for this?

=RELATED(Y) wont work as it is a one to many join. 

Thanking you in advance!

[V-lianl-msft]

Hi @Daz4 ,

Try using the LOOKUPVALUE function.

If the problem persists,could you share the sample data and the result you expected?

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

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

[Daz4]

So I managed to work out a solution but now have a new problem. 

I can get the values i need by the following:

query = Calculate(MAXX(FILTER(TABLENAME,COLUMNZ="a"),COLUMNY))

However, the new problem is i need to do this for each value in ColumnZ, and since I hve already filtered that table I cant filter it a second time. 

Anyone know how to get around this another way?

[amitchandak]

@Daz4 , related will work on many sides. On one side you can use relatedtable or filter within sumx/minx etc

refer: https://www.youtube.com/watch?v=Wu1mWxR23jU

https://www.youtube.com/watch?v=czNHt7UXIe8

[Daz4]

Hi There Amit, Since my query requires filtered response, I dont think that will work unless I do not understand enough. I basically need to return the value in column y when column z = a. Join is on column x. Its not an aggregated result its a single value such as date or boolean.