Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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.
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
Table 3 containing Table 1 and Table 2 info is not just a visual: it will be used to perform queries as well.
Thanks,
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.
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.
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])
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |