Geting a field from a related table in a one to many relationship.

[jmvidal]

Hi,

I need to retrieve the field value from Table2 when option=sector, in order to build Table3 from Table1 and Table2.

LOOKUPVALUE is working fine, but wonder if it wouldn't be more eficient using RELATED or RELATEDTABLE, as far as those tables are linked throught the customer field.

Thanks,

Table1

customer	country
1	USA
2	UK
3	China

Table2

customer	option	value
1	sector	construction
1	size	small
2	sector	agriculture
2	type	private
3	sector	construction

Table3

customer	country	sector
1	USA	construction
2	UK	agriculture
3	China	construction

[v-juanli-msft · Accepted solution]

Hi @jmvidal 

I use Power query to create the Table3

make a duplicate table of Table2,

remove other columns,

then select "Option' column in Table3, pivot column,

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmvidal]

Yes! Using Pivot coulmn I created a new table that I added I to the schema linked to the original table by the customer code.

Thank you very much.

[v-juanli-msft]

Hi @jmvidal 

Do you want to create a physical table or a table only to show the result as "Table3"?

If you could accept to create measures and finally show these via a table visual, it could perform well than a calculated table.

Best Regards
Maggie

[jmvidal]

Table 3 containing Table 1 and Table 2 info is not just a visual: it will be used to perform queries as well.

Thanks,

[amitchandak]

If table 1 has unique customers, then you can join table 1 and table 2 on customers and in visual , you can visual level filter for sector.

[jmvidal]

Thank you @amitchandak 

Please check my reply to previous solution regarding the several columns I need to insert and the imposibility to use just one filter.

[amitchandak]

There are few solution.

1. You could have pivot these two columns. But that will give you 50 Columns.

2. Use lookup or a formula like this. Again that will give you 50 columns

New column in Table 1 = maxx(filter(table2,table2[customer] = table1[customer] && table2[option]="construction",table2[value])

3. The formula above can also work as a measure. At line customer level. I did not try but should work. Here you should able to pass the second value as parameter.

Measure = maxx(filter(table2,table2[customer] = max(table1[customer]) && table2[option]="construction",table2[value])

[jmvidal]

Thanks @amitchandak , 

Any Idea if this would perform better than LOOKUPVALUE?

[Mariusz]

Hi @jmvidal 

Please see the attached file with two solutions one using CALCULATE and second Power Query.

And yes RELATED and RELATEDTABLE will perform better than the LOOKUPVALUE.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[jmvidal]

Hi again @Mariusz ,

Thank you for your answer.

I made the question incomplete: I really need to add not just sector but also size and type, in the example (in real life I have 50 others to add)

Also customers table have many other columns.

For that reason the filter you suggested to use in the Power Query solution and Dax looks to me won't be enough...

I wish I had a solution similar to LOOKUPVALUE where I could select the desired column value together with the filter column&value.

Thanks