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.

[t0talz]

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]

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

[Anonymous]

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.

[SamLester]

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

[Anonymous]

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

)

[Anonymous]

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 · Accepted solution]

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

[ITstod-IE]

Is it possible to adjust this query to search for several words?

[Hede2000]

Hello all

I am trying to figure out how to return values to a new column C, if certain conditions are met in columns A and/or B:

1. If text contains "KF" in column A, then the whole value in column A should be returned to column C

2. If text does not contain "KF" in column A, but the text in column B contains "KF", then the part of the text in column B that contains "KF" plus 6 additional characters should be returned to column C

3. If text in column B does not contain "KF" then the value from column A should be returned to column C

Example:

Column A	Column B	Column C
KF123456		KF123456
blabla	bla KF234567 bla	KF234567
1254	blabla	1254

Thanks in advance!

[Ashish_Mathur]

Hi,

Write these calculated column formulas:

Position of K in KF = SEARCH("KF",Data[Text2],1,1)

Position of space after KF = SEARCH(" ",Data[Text2],[Position of K in KF],50)

Column = if(CONTAINSSTRING(Data[Text1],"KF"),Data[Text1],if(CONTAINSSTRING(Data[Text2],"KF"),mid(Data[Text2],[Position of K in KF],[Position of space after KF]),Data[Text1]))

Hope this helps.

[Hede2000]

Hello

Thank you so much for your reply.

It almost works! It works for scenarios 1 and 3. Great!

It also works for scenario 2 if there is no text following the KF-value. If the KF-value has text after it, this text is also returned, which it should not.

In the scenario 2B below the value "KF234567 bla" is returned. I need it to return only "KF234567", so a 8-character result is the decided one.

Scenario	Column A	Column B	Suggested solution	Desired solution	Works
1	KF123456		KF123456	KF123456	Yes
2A	blabla	bla KF234567	KF234567	KF234567	Yes
2B	blabla	bla KF234567 bla	KF234567 bla	KF234567	No
3	1254	blabla	1254	1254	Yes

Thank you so much in advance.

[Ashish_Mathur]

You are welcome.  Revise the last formula to

Column = if(CONTAINSSTRING(Data[Text1],"KF"),Data[Text1],if(CONTAINSSTRING(Data[Text2],"KF"),mid(Data[Text2],[Position of K in KF],7),Data[Text1]))

If this works you may delete the "Position of space after KF" column.

[Munawar1881]

Hi,

hope you are doing well, I have some thing very similar to ask that if I have a description of the product in column one having word LADIES then I need to have text WOMEN in column B. can you please please let me knwo the formula to do that for multiple words  like BOYS, GIRLS, WOMEN and MEN

[Ashish_Mathur]

Hi,

Share some data to work with.  Also, share another 2 column table with Customer Type in column 1 and Category in column 2.

[Hede2000]

Dear Ashish

That works perfectly. Thanks for all your kind help. Appreciate it.

[Ashish_Mathur]

You are welcome.  Please mark my previous reply as Answer.

[SantoshKumar]

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;

[Munawar1881]

Hi,

hope you are doing well, I have some thing very similar to ask that if I have a description of the product in column one having word LADIES then I need to have text WOMEN in column B. can you please please let me knwo the formula to do that for multiple words  like BOYS, GIRLS, WOMEN and MEN

Thank you 

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[DolEgon22]

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