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

[BKnecht]

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.

[fredcedeno]

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.

[Ashish_Mathur]

Hi,

Use Fuzzy matching in the Query Editor.

[Vegeta07]

You can achieve that by following dax:

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

[Anonymous]

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

[h2022]

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

[atillotson]

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

[h2022]

I would try this:

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

[atillotson]

this worked - thanks!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

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,

[Ashish_Mathur]

The LOOKUPVALUE() function should help.

[Anonymous]

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]

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:

FakeJira

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:

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]

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

[Stambla]

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 

[PHEstaciMa1]

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

[Stambla]

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

[SantoshKumar]

@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.

Thanks

Santosh

[Ashish_Mathur]

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.

[Anonymous]

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

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