Forum Discussion
Lookup using multiple numbers that are separated by special character
Hi all,
Is there a way I can lookup multiple values that are separated by ";" and return the values and have them be separated by ";".
I want to use 'Table 2' to return the values in the 'Names' column using the 'Numbers' column as a key. See the 'desired outcome' table below for what I am looking for. Thank you!!
Table 1
| Numbers |
| 000; 111; 333 |
Table 2
| Numbers | Names |
| 000 | Emily |
| 111 | Chrissy |
| 222 | Kathy |
| 333 | Matt |
Desired Outcome
| Numbers | Names |
| 000; 111; 333 | Emily; Chrissy; Matt |
Best,
Emily
Hi emily324,
You can try to use following measure formula to get the correspond name list based on current number string:
formula = VAR currNumber = SELECTEDVALUE ( Table1[Numbers] ) RETURN CONCATENATEX ( CALCULATETABLE ( VALUES ( Table2[Names] ), FILTER ( ALLSELECTED ( Table2 ), SEARCH ( Table2[Numbers], currNumber, 1, -1 ) > 0 ) ), Table2[Names], "," )Regards,
Xiaoxin Sheng
5 Replies
Hi emily324,
You can try to use following measure formula to get the correspond name list based on current number string:
formula = VAR currNumber = SELECTEDVALUE ( Table1[Numbers] ) RETURN CONCATENATEX ( CALCULATETABLE ( VALUES ( Table2[Names] ), FILTER ( ALLSELECTED ( Table2 ), SEARCH ( Table2[Numbers], currNumber, 1, -1 ) > 0 ) ), Table2[Names], "," )Regards,
Xiaoxin Sheng
The formatting came out weird for the tables... numbers and names are two separate columns. Thanks!
Any way to remove duplicate names if some of the same names are returned?
