Forum Discussion
Using SELECTEDVALUE() for lookup in different table
Hi Community! I have a question regarding the usage of the SELECTEDVALUE() function. I have created a measure Date as in the picture below:
Table1 contains a column, col1, that has around 130 distinct values (strings). Table2 contains a column, col2, that has around 600k distinct values (strings) and includes all values in Table1[col1]. These tables are updated daily and imported as dataflows.
I would want to be able to select a value from a slicer (containing Table1[col1]), then find that value in the Table2[col2] column and return the associated value (y) from another column, col3, in Table2. I have no relationship between the two tables (there are lots of duplicates in Table1[col1]). It seems that I cant use the function SELECTEDVALUE() when trying to search in another table with a value that has been selected in a slicer (Slicer 2). See schematic picture below:
What I have done:
In my implementation so far, I have actually another slicer (Slicer 1) that contains another column (col0) in Table1. After selecting a value (z) from that slicer, I get the associated values (x's) in Table1[col1] in another slicer (Slicer 2). But then, when selecting a value (x1) from Slicer 2, I cant seem to catch that value with the SELECTVALUE() function, to be able to search in the other table, Table2. If I instead just type the string that should have been caught by SELECTEDVALUE() in the CONTAINS() function, it works. But not otherwise...
I have tried to create creating a new table containing the UNION of Table1[col1] and Table2[col2], and implementing a measure as in picture above. This seems to find a value when using SELECTEDVALUE() function but then there is still the problem of getting the associated value from col3 in Table2.
- Anonymous2 years ago
Hi Anonymous ,
The Table data is shown below:
Use the following DAX expression to create a measure
SELECTEDVALUE function - DAX | Microsoft Learn
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
Measure = VAR _col1ForSlicer = SELECTEDVALUE(Table1[col1]) RETURN LOOKUPVALUE(Table2[col3],Table2[col2],_col1ForSlicer)Final output
If the problem persists, could you please share your .pbix file without sensitive data?
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- AnonymousNot applicable
Sorry if my explanation is messy, I am thankfull for all tips I can get!
- AnonymousNot applicable
Hi Anonymous ,
Can you share the data of 'Table1'? Or a .PBIX file without sensitive data?
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
- AnonymousNot applicable
Hi Anonymous thank you for your reply. This is essentially what the tables contain. So I would for example want to select a value from column col0 (244413), which then slices the col1 (F4560, F1253), I select one of those from a new slicer. Then the selected value (e. g. F4560) get looked up in Table2[col2], and the associated value from col3 (2545) should be returned.
Table1
col0 col1 235505 F8769 232641 F6453 232641 F1938 232641 F3275 244413 F4560 244413 F1253 246787 F5412 232648 F6023 232648 F5643 Table2
col2 col3 F4560 2545 F4365 2531 F5643 2552 F6539 2609 F1209 2636 F4532 2614 F1938 2437 F6578 2546 F5490 2631 - AnonymousNot applicable
Hi Anonymous ,
The Table data is shown below:
Use the following DAX expression to create a measure
SELECTEDVALUE function - DAX | Microsoft Learn
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
Measure = VAR _col1ForSlicer = SELECTEDVALUE(Table1[col1]) RETURN LOOKUPVALUE(Table2[col3],Table2[col2],_col1ForSlicer)Final output
If the problem persists, could you please share your .pbix file without sensitive data?
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- AnonymousNot applicable
Hi Anonymous ,
I saw you shared your .pbix file, but your reply has been deleted. If your problem has been solved, please mark my answer as the solution, thank you.
Best Regards,
Wenbin Zhou