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

26 REPLIES 26
Highlighted
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])

)

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

 

Highlighted

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.

 

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

Highlighted

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!!

 

 

Highlighted

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/
Highlighted

I solved a similar scenario with the help of nested IF and OR

 

RANK = IF(OR(STUDENT[GRADETYPE] = "VERY GOOD", STUDENT[GRADETYPE] = "GOOD"),"One" , "Two")
Highlighted
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.

Highlighted

Hi,

 

Share a dataset and show the expected result.


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

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;

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors