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

I am in the same situation, I only have 2 tables in my model

In my first table I have employee names

in my second table I also have employee names plus more information I need from them

I need to compare if names in column 1 table 1 are in table 2

 

I have used:

Employee Full Name = (RELATED('Emp_Lookup'[Employee Full Name]))
 
it brings some names. but here is the Big BUT. I unfortunately have some variations on their names
example
table 1 Colum 1
Mario Perez
 
but in table 2 colum 1
Mario J Perez
 
then since they dont match it returns a blank
 
I also have on both table first names and last name but again due to this slighly difference it just return a blank 
 
any idea on how to by pass this. data is not too long so there wont be too many coincidences on their names.

 

Hi,

Use Fuzzy matching in the Query Editor.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Vegeta07
Frequent Visitor

You can achieve that by following dax:

If(containstring(columnname, "X"), "desired", "none")

Anonymous
Not applicable

Hi trying to search in a column for specific text and return the matching value in another column? Any ideas would be greatly appreciated

Did you find a solution to this? I am currently working on something similar, I need to search for "furniture" in a specific column as there can be different types of furniture in that column but I want to return anything that contains "furniture". I did this and it works....

Type = if(search("*furniture*", Data[Products], 1, 0) = 1, "Furniture", "Other")

Hi! this has worked for me but how do I update it to include two options, eg/ "furniture" or "cart"? thanks!

I would try this:

= if
(search("*furniture*", Data[Products], 1, 0) = 1, "Furniture", if(search("*Cart*", Data[Products], 1, 0) = 1, "Cart", "Other"))

this worked - thanks!

Hi,

Share some data and show the expected result.


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

Hey,

Thanks for your reply.

 

I want to search in Product Code for specific text (as there are some entries with double data e.g. TARF2/TARF2L) and return the associated product category.

 

Thanks,

 

gh614_0-1652777961724.png

 

The LOOKUPVALUE() function should help.


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

Hello everybody,

 

i´m trying to replace a word in a range of text, for example:

 

Row1: AJUSTE MINAM

Row2: AJUSTE KC

Row3: AJUSTE OSINER

 

New Column: AJUSTE

 

As i say, i wanna replace a range of text by a word: "AJUSTE"

 

if you have an idea i´ll be very grateful

 

Thank a lot

pcardno
Frequent Visitor

I know this is quite old, but this is the closest thread I came to solving what I think is the same need I had, but the proposed solutipms don't solve it. My use case - I have Table A, which has column X, and Table B, which has columns Y and Z. I want to do a fuzzy match on 'Table A'[X] against 'Table B'[Y] and return the value in 'Table B'[Z], so basically a LOOKUPVALUE using search.

 

In my example, Table A is called "Merged NPA5 and JIRA Query" and column X is "NPAID". Table B is called "FakeJira" and columns Y and Z are, respectively, "External Ticket Id" and "Value To Return". So it looks like this:

 

Merged NPA5 and JIRA Query:

2019-08-28_2118.png

FakeJira

2019-08-28_2115.png

 

And this is the result I wanted, where it can find a reference within External Ticket ID, but also handle blanks and duplicates, so gets this as a result:

 

2019-08-28_2123.png

Here's the query:
 
LookupBasedJiraID = FIRSTNONBLANK(selectcolumns(filter(FakeJira,search('Merged NPA5 and JIRA Query'[NPAID],FakeJira[External Ticket Id],,0)),"MyColumn",FakeJira[Value To Return]),TRUE())

 

The only thing now is to sort in the case where there's a duplicate, but that'll be trivial based on some other data.

 

Anyway, hope this helps - I think this is what the original requestor might have been wanting to do.

 

 

Paul.

PHEstaciMa1
Helper I
Helper I

Hi, I have the same problem. I needed to classify these texts as "recordable" if not "non recordable"... 

these are recordable

Fatality     

Irreversible

Lost time

Medical treatment beyond first aid

Restricted work case

else 

Non-recordable...

 

my syntax goes like this.

 

Recordable Injuries IF(Injuriesver2[Severity]="Fatality",IF(Injuriesver2[Severity]="Irreversible",IF(Injuriesver2[Severity]="Lost time",IF(Injuriesver2[Severity]="Medical treatment beyond first aid",IF(Injuriesver2[Severity]="Restricted work case","Recordable","Non-recordable")))))

 

You ever finish this project? Im working a new PBI graphic for some HSE items and would be interested to see where you ended up 

Yes, I did complete this one... I used these DAX coming from the comments here.... 

 
Injury Type = SWITCH([Severity],"Fatality","Recordable","Irreversible","Recordable","Lost time","Recordable","Medical treatment beyond first aid","Recordable","Restricted work case","Recordable","Non recordable")
 
PBI Sample.PNG

 

 

This is pretty cool. I may give you another shout. Your dashboard looks good 

@PHEstaciMa1  based on your details , i have framed a small table  with One Column as Severity and other is a calculated Column 'Category' and used below formula to get desired output.

Category =
IF(('Table'[Severity]="Fatality" ||
'Table'[Severity]="Irreversible" ||
'Table'[Severity]="Lost time" ||
'Table'[Severity]="Medical treatment beyond first aid" ||
  'Table'[Severity]="Restricted work case"),
  "Recordable","Non-recordable")
attaching the result snapshot for reference.
let me know whether it solved your issue.

CalculateColumn.PNG

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Thanks
Santosh

Hi,

Prepare a simple 2 column table with Text entries in the first column and Category in a second column.  The second column should have Recordable and Non-recordable.  Thereafter, build a relationship from the text column of your base data to the text column of thsi new 2 column dataset.  Write the =RELATED('Table2'[Category]) calculated column formula to fetch data from the Category column to Table1.

Hope this helps.


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

This can now be done in DAX with the CONTAINSSTRING function:

MyCalculatedColumn = If(CONTAINSSTRING([TARGETCOLUMN];"searchforthis");TRUE();FALSE())

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.