cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BKnecht Regular Visitor
Regular Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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"
)
19 REPLIES 19
Super User
Super User

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

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 Regular Visitor
Regular Visitor

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

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 Regular Visitor
Regular Visitor

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

Hello 

 

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

 

Thanks 

satish Frequent Visitor
Frequent Visitor

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

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.

Super User
Super User

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

@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 Frequent Visitor
Frequent Visitor

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

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.  

Super User
Super User

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

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

DolEgon22
Advisor

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

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 Frequent Visitor
Frequent Visitor

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

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

)