Searching for a value with multiple possible results (look up value)

[Elie_HUCHET]

Hello,
I have a problem with a LOOKUPVALUE formula because I have multiple possible results and it is showing an error.
If possible, I would like a formula that can find all the records in the "CLEAR-Dossier - Actifs [Dossier]" table based on the SIRET number in both the "CLEAR-Clients" and "CLEAR-Dossier - Actifs [Dossier]" tables. The problem is that there is no link between these two tables.
I can create a relationship by adding a new Active Dossier table, but I prefer not to do so because it is a rather large table. So the optimal solution would be to find all the records using only the SIRET number.

Thank you for your help.
Elie HUCHET.

[v-xinruzhu-msft]

Hi @Elie_HUCHET

1.When you use LOOKUPVALUE() function,If multiple rows match the search values and in all cases result_column values are identical, then that value is returned. However, if result_column returns different values, an error or alternateResult (if supplied) is returned.

2.and if your result will return multiple rows, it had better not use the calculated column, you can use measure,

3.You can use filter() function ,it can return multi values but the result will be displayed as  a table.

You can refer to the following link to know more about the FILTER() function.

FILTER function (DAX) - DAX | Microsoft Learn

Best Regards! 

Yolo Zhu 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.