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

 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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
Not applicable

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!




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

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 AColumn BColumn C
KF123456 KF123456
blablabla KF234567 blaKF234567
1254blabla1254

 

Thanks in advance!

 

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.

Untitled.png


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

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.

 

ScenarioColumn AColumn BSuggested solutionDesired solutionWorks
1KF123456 KF123456KF123456Yes
2Ablablabla KF234567KF234567KF234567Yes
2Bblablabla KF234567 blaKF234567 blaKF234567No
31254blabla12541254Yes

 

Thank you so much in advance.

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.


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

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

Munawar1881_0-1665945868234.png

 

Hi,

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


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

Dear Ashish

 

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

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


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

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;

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

Munawar1881_0-1665945868234.png

Thank you 

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

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.