cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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

 

@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())
t0talz
Regular Visitor

Hi,

why is the FIND function not working in this case?

 

This is what I tryed:

 

IF(
  AND(
    'Data'[Hostname]="somepage.com";
    FIND("/";'Data'[Landing Page])>=0
  );
1;0)

The FIND function is not expecting a column? Why?

stkbailey
Regular Visitor

 Here's another solution using SEARCH that doesn't make your mind go into a double-negative vortex when doing multiple IF statements. It uses the fourth argument for SEARCH, which returns a specific value if it doesn't find the string, rather than an error:

 

IF((SEARCH("A", [column], 1, 0) > 0), "A",
IF((SEARCH("B", [column], 1, 0) > 0), "B", "C"))

 

Hi Guys, I'm a bit late to the party but I had a similar problem and found a solution using =SWITCH(TRUE()  and the SEARCH() funcion as @stkbailey showed that works very well.  For more information on the SWITCH(TRUE(),***) function, check out the posts on PowerPivotPro about it The Diabolical Genius of “SWITCH TRUE”

 

 

Field = SWITCH(
    TRUE(),
    SEARCH("A", [column], 1, 0) > 0), "A",
    SEARCH("B", [column], 1, 0) > 0), "B",
    SEARCH("C", [column], 1, 0) > 0) && NOT(ISBLANK([column])), "C",  -- Example to show how to add multiple conditions
    ,"D"
)
    

I will use this pattern many times in the future so Thanks stkbailey for your answer and thanks Rob Collie for your blog.

 

 

Great thread, I just used this pattern tonight, but noticed that the SEARCH function lines are missing open parens and had an extra comma in the else case.

Updated syntax:

 

Field = SWITCH(
    TRUE(),
    (SEARCH("A", [column], 1, 0) > 0), "A",
    (SEARCH("B", [column], 1, 0) > 0), "B",
    (SEARCH("C", [column], 1, 0) > 0), "C",
    "D"
)

 

RazielReaver
Regular Visitor

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

 

Measure = IF(

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

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

                   SUM('TableName'[Column])

)

RazielReaver
Regular Visitor

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

)

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

I have a table and in that one column contains few values as below.
requirement : i want get the count of rows with some conditions like below:
1. Count of row which contains only "first"

2.  Count of row which contains  "first" and "Second"

3. Count of row which contains "first" but not "Second" or "third" etc.

Sample column posted below:

 

Column
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
second;third;fourth;fifth;
first;third;fourth;fifth;
first;fourth;fifth;
first;second;third;fifth;
second;third;fourth;fifth;
first;fourth;fifth;
first;second;
first;second;third;fifth;
first;second;fourth;fifth;
third;fourth;fifth;
fourth;fifth;
first;second;third;fourth;fifth;
first;second;third;fourth;fifth;
Anonymous
Not applicable

Is there a way to do this with multiple items?  

I have columns where I need to different strings and bring them into one column.

Hi,

 

Share a dataset and show the expected result.


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

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

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/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.