Compare multiple column/value with separator ";"

[Nomad]

Hello there,

I would like to compare value between several columm and higlight the value when one of the column have the same value as the "Reference" column.

So for the exemple, if the "column1" and/or "column2" contain same value from the "Reference" column, I would want to display it in the "Result" column.

So i manage to display the value when the result is strictly identical between columm.

My issue is, there is line who contain several value separate by ";", if the reference column contain for exemple 112-35-5, it display the result, but if the "Reference" column contain 112-35-5;106-97-8 and the "column1" 112-35-5, it will not display the result.

Is there some way in Dax or Query to workaround the ";" ?

At the moment im only doing something like this :

Result =

if (Reference = Column, "🚩 Restriction 🚩", "")

Thank you very much for your support.

[Nomad]

I find a workaround who seems to solve the issue I have : Solved: duplicate rows when column has double value separa... - Microsoft Fabric Community

But feel free to add in the conversation if you have some other solution.

[Bmejia]

You can build a measure like this, you can integrate as need it column2 in your if statement.

Results =

VAR Column1 =CONTAINSSTRING(SELECTEDVALUE(TableContains[Reference]),SELECTEDVALUE(TableContains[Column1]))

Var Column2 = CONTAINSSTRING(SELECTEDVALUE(TableContains[Reference]),SELECTEDVALUE(TableContains[Column2]))

RETURN

If(Column1=TRUE(),SELECTEDVALUE(TableContains[Column1]),"")

[Nomad]

Hello,

Thank you for your input, its more elegant than my measure, but, it only show stricly identical value.

The issue is, if I have for exemple 112-34-5;13463-67-7;34590-94-8 in the reference colmun and and only 112-34-5 the result is not going to show me its duplicate because of the ";" between value.

EDIT : Its like the same issue in excel, if you highlight duplicate value, only highlight stricly identical value.

EDIT 2 : How can I add the .pbix for maybe more context ? It seems i can't attach it in the conversation.