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.

SantoshKumar Regular Visitor
Regular Visitor

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

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 Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 256 members 2,465 guests
Please welcome our newest community members: