LookupValue is not showing the column needed on the other table

[Anonymous]

I wanted to add another column that is a lookup from another table but whenever I type in the table, it doesn't show me the column that I wanted.

Table 1

ContractNo  Parent

1                   A

2                   A

3                   B

Table 2

ContractNo GM   Month

1                  100   Jan

2                  200   Jan

3                  300   Jan

1                  50    Feb

2                  60    Feb

3                 300   Feb

***** Add column Parent in Table2

MeasureTest = LOOKUPVALUE('Table 1'[Parent], 'Table 1'[Contract], *****) <--- Table 2 is not showing column ContractNo

I have a relationship between Table1 and Table 2 (1:Many relationship)

Please can you help how to resolve this? If LookupValue is not possible, what other method can be used?

I've also tried using Relate but still, the columns on Table 2 is not shown.

Thanks in advance.

[Anonymous]

I've just created a new custom column using the Power Query, sort of a vlookup to populate this Parent column so that I can use it in my slicer. It's working as expected.

Thanks all for your help.

[ERD]

Hi @Anonymous ,

Parent = RELATED(Table1[Parent])

OR

Parent_v2 =
VAR current_contractNo = Table2[ContractNo]
RETURN
    CALCULATE ( MAX ( Table1[Parent] ), Table1[ContractNo] = current_contractNo )

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

[Samarth_18]

Hi @Anonymous ,

You can try code like below :-

column =
CALCULATE (
    MAX ( '_Table 1'[Parent] ),
    FILTER ( '_Table 1', '_Table 1'[ContractNo] = '__Table 2'[ContractNo] )
)

Output:-

Thanks,

Samarth

[Anonymous]

Hi Samarth_18,

I'm having the error - "A single value for column in table 'Table 1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation to get a single result.

[amitchandak · Accepted solution]

@Anonymous ,

new column in table 2

= maxx(filter(Table1, tabel1[contract no] = table2[Contract]), Table1[parent])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

Hi amitchandak,

I've watched your video and was able to get the expected results. Thanks for that.

This is now my new measure:

Test = MAXX(RELATEDTABLE('Table1'), 'Table1'[Parent])

My issue now is that, no matter what I do, I am not able to put it in my slicer.

I need to use this in my slicer such that if I choose the Parent A, my details will display contracts 1 and 2 as per my table above.

How can I achieve this?

[Anonymous]

Hi Mafs,

I could be wrong here, but could it to be to do with the direction of the relationship? Im not sure if it's good practice to do this but is the cross filter direction on the relationship page currently set to 'single'? If so try setting it 'both' and then see if lookupvalue/related work.

[Anonymous]

I am now able to work out using this "MAXX(RELATEDTABLE('Table1'), 'Table1'[Parent])".

My issue now is that this newly created measure is not accessible in the slicer.

Any ideas?

Thanks.