Using Substitute function to remove parts of string to make comparable but then unable to compare text values

Question

I am using the SUBSTITUTE function in DAX to clean up a column to remove parts that are not needed for comparison.

Using DAX = IF((SUBSTITUTE(SUBSTITUTE([Column],"ace",""),"nine",""))=[Column to compare],"True","False")

Column              Column to compare
ace 24 block 48     24 block 48
nine 18 big 4       18 big 4

but it throws and error saying '.' not valid syntax???

** Also need to mention that column to compare is created using power bi/power query (just not sure if there is a disconnect between the two?) The data type of [Column to compare] is type text

Answer 1

I can't reproduce your error, but you also need to remove the extra space in from for this to work.

SUBSTITUTE ( SUBSTITUTE ( [Column], "ace ", "" ), "nine ", "" ) = [Column to compare]

Or else use TRIM to remove whitespace at the ends of the strings:

TRIM ( SUBSTITUTE ( SUBSTITUTE ( [Column], "ace", "" ), "nine", "" ) ) 
 = TRIM ( [Column to compare] )

Depending on exactly what you are trying to do, CONTAINSSTRING may be useful too:

CONTAINSSTRING ( [Column], [Column to compare] )