Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BKnecht
Helper II
Helper II

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
KHorseman
Community Champion
Community Champion

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? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

50 REPLIES 50

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.

I need a formua to create a new column that checks a exising column in a table and provides a new value based on multiple condtions.

 

The formula shall find specified text contained in a longer text string, the searched text can be at the beginning the end or the end of the string.

If the searched text combination is identified, a new text should be put in the new column field.

 

Logic:

 check field if somewhere in the text string  "*abc* " is contained

         if contained, put "xyz"

         if not contained

         check if  " *def* " is contained, if contained, put "*ufw*" in the new colums

if non of the searched combinations is identified, put "hij" in the new column

 

many thanks!!

 

 

Hi,

 

Given the data below, i wrote the following calculated column formula in the first Table.  You may download my solution workbook from here.

 

=LOOKUPVALUE(keywords[Result],keywords[Keywords],FIRSTNONBLANK(FILTER(VALUES(keywords[Keywords]),SEARCH(keywords[Keywords],Data[Data],1,0)),1))

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I solved a similar scenario with the help of nested IF and OR

 

RANK = IF(OR(STUDENT[GRADETYPE] = "VERY GOOD", STUDENT[GRADETYPE] = "GOOD"),"One" , "Two")
KHorseman
Community Champion
Community Champion

@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? Mark my post as a solution!

Proud to be a Super User!




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
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hello 

 

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

 

Thanks 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.