Compare almost identical text values and expect a true/false result in a separate column.

[Ermal]

Hi everyone,

Have been struggling for a while now. Hoping to find someone who can help.

I'm looking for a better formula to compare two text values of different datasets that are not identical, but similar.

So, as you can see below, I'm comparing data from Element A (dataset 1) with Element B (dataset 2) and hoping to get three times "True" as a value in a seperate column.

The formula that I'm currently using is:

" Column =

var sapNM = (trim(related('Dataset 1'[Element A])))

Return
CONTAINSSTRING(sapNM, (trim('Dataset 2'[ELEMENT B]))) "

When using this formula, I'm getting to many false values. I was thinking about turning the formula around but then I don't get the desired datasets anymore to choose from.

[Ermal]

Hi Dangar,

Thank you for the formula.

Applied it and noticed some improvement on my report.

Still experiencing problems with the example below. Although there is so little difference, Power BI gives me false as a result when comparing these two values.

Thanks for any contribution.

Ermal

[Dangar332]

Hi, @Ermal 

I think it happens because of dot(.) In element a 

For checking purpose 

Left(dataset [element a],len(dataset [element a])-1) 

Use above code for checking purpose it remove dot(.) From element a

After removing dot compare it with element b

If it gives true means dot (.) Create problem in above you mention 

[Ermal]

Hi @Dangar332 ,

FYI
IF the dot(.) is in Element B and not in Element A then I'm not experiencing any issues, see below:

If it is the other way arround, then it gives me false as a result:

If Element A is an empty field compared to element B then it gives me True as a result:

The other way around it gives me a false value

I've been struggling with this for so long.
Perhaps I should settle for one good formula and accept that the system will never be able to accommodate everything.

[Dangar332 · Accepted solution]

hi, @Ermal 

check below image 

blank value considerd as string so it give TRUE() as you face above 

try to replace blank() value with string that not present in comparision string .

[Dangar332]

hi, @Ermal 

if  relationship present bw different table then try below for column

column =
   CALCULATE(
       CONTAINSSTRING(
           MIN('e 2'[element 2]),
           MIN('e 1'[element 1])
       )
   )