If text column CONTAINS specified value, give me what I want

[BKnecht]

Hi Everyone,

I'm trying to create a calculated column in one of my tables that says:

IF( row CONTAINS "A", put "A", otherwise put "B")

The problem is I can't figure out what the contains function is in DAX, and I've looked everywhere. Can anyone help me out?

Thanks in advance.

[KHorseman · Accepted solution]

The easiest way to do this would be to do the column in the query rather than the resulting data model table. Hit the Add Custom Column there and the code would be

if Text.Contains([ColumnName], "A") then "A" else "B"

If you want to do it in DAX it's a bit more messy. My best idea is to search for the position of the text you want to find, and check for that to return an error when the text is missing.

CustomColumn = IF(
	ISERROR(
		SEARCH("A", TableName[ColumnName])
	),
	"A",
	"B"
)

[BKnecht]

I had to nest it a few times -  that worked perfectly. Thanks for being so specific with the code too, that really helped. Much appreciated!

[MG83]

Hello 

Is there a similar DAX to search for Text within different tables? 

Thanks 

[satish]

I think the expression was written wrong. 

it should be 

CustomColumn = IF(
	ISERROR(
		SEARCH("A", TableName[ColumnName])
	),
	"B",
	"A"
)

 Or

CustomColumn = IF(
	IFERROR(
		SEARCH("A", TableName[ColumnName])
	, -1) > -1,
	"A",
	"B"
)

Please correct me if I am wrong.

[KHorseman]

@satish I think you're right. Looks like I got A and B backwards. But I'd go with your first suggestion. IFERROR introduces unnecessary complexity to the formula in my opinion.

[satish]

IFERROR does add complexity.

In my case I had  multiple conditions combined so I had to use IFERROR and I thought someone else might it need it too.  

[KHorseman]

@satish yeah, definitely never hurts to have an alternative method for similar cases.

[DolEgon22]

Love this solution:

if Text.Contains([ColumnName], "A") then "A" else "B"

However, what if I have more than two possibilities?

For example, ColumnA can be "Car", "Truck", "Bus". How would I create a column that would give a value of "1" for "Car", "2" for "Truck" and "3" for "Bus"?

[RazielReaver]

This is what I tried and it works.. it discrimine everything else and works when find "4"

DAX:

Measure = IF(

       CONTAINS('TableName','TableName'[Column],"4"),

                   SUM('TableName'[Column])/1.80,

                   SUM('TableName'[Column])

)