cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
KHorseman Super Contributor
Super Contributor

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

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

View solution in original post

24 REPLIES 24
Highlighted
KHorseman Super Contributor
Super Contributor

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

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

View solution in original post

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.

KHorseman Super Contributor
Super Contributor

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.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
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.  

KHorseman Super Contributor
Super Contributor

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.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
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])

)

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 294 members 3,937 guests
Please welcome our newest community members: