Using SELECTEDVALUE() for lookup in different table

[Anonymous]

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. 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

Sorry if my explanation is messy, I am thankfull for all tips I can get!