topic Re: Compare almost identical text values and expect a true/false result in a separate column. in DAX Commands and Tips

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

Ermal — Tue, 02 Jan 2024 17:09:14 GMT

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.

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

Dangar332 — Tue, 02 Jan 2024 18:08:35 GMT

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]) ) )

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

Ermal — Tue, 02 Jan 2024 18:54:39 GMT

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

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

Dangar332 — Tue, 02 Jan 2024 19:05:40 GMT

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

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

Ermal — Tue, 02 Jan 2024 19:40:21 GMT

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.

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

Dangar332 — Wed, 03 Jan 2024 09:13:42 GMT

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 .