cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RazielReaver Frequent Visitor
Frequent Visitor

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

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

)

stkbailey Visitor
Visitor

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

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

 

tazmon95 Regular Visitor
Regular Visitor

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

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.

 

t0talz Frequent Visitor
Frequent Visitor

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

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?

chwolf Visitor
Visitor

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

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

 

 

Super User
Super User

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

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

chackorobin Frequent Visitor
Frequent Visitor

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

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")
lfahre Frequent Visitor
Frequent Visitor

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

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
Super User
Super User

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

Hi,

 

Share a dataset and show the expected result.