cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Multiple search criteria consisting of "AND" "OR"

There are two columns: Subject and Type

 

Here is the criteria

In the Subject column if ("Demo", or "Presentation", or "Webex") appears, OR in the Type column ("Demo", or "Presentation", or "Webex") appears, than the outcome should be "DEMO".

In the Subject column if ("email", or "demo", or "visit", "presentation", or "webex") does NOT appear, AND in the Type column ("call") appears, than the outcome should be "CALL".

In the Subject column if ("email", or "message sent") appears, than the outcome should be "EMAIL".

 

How do you write this in a single formula?


Thanks!

9 REPLIES 9
Highlighted
Super User III
Super User III

Re: Multiple search criteria consisting of "AND" "OR"

@data23f

 

Try this colummn

 

Column =
VAR Condition1 =
    OR (
        Table1[Subject] IN { "Demo", "Presentation", "Webex" },
        Table1[Type] IN { "Demo", "Presentation", "Webex" }
    )
VAR Condition2 =
    AND (
        NOT Table1[Subject] IN { "email", "demo", "visit", "presentation", "webex" },
        Table1[Type] = "call"
    )
VAR Condition3 = Table1[Subject] IN { "email", "message sent" }
RETURN
    SWITCH ( TRUE (), Condition1, "DEMO", Condition2, "CALL", Condition3, "EMAIL" )
Try my new Power BI game Cross the River
Highlighted
Helper I
Helper I

Re: Multiple search criteria consisting of "AND" "OR"

Thanks for your response!

 

I used your formula but I got the following error:

 

The syntax for 'Call' is incorrect. (DAX(VAR Demo = OR ( Event[Subject] IN { "Demo", "Presentation", "Webex" }, Event[Type] IN { "Demo", "Presentation", "Webex" } )VAR Call = AND ( NOT Event[Subject] IN { "email", "demo", "visit", "presentation", "webex" }, Event[Type] = "call 

 

I think there was more in the error message, but I couldn't copy it.

 

This is how I entered the formula:

 

DAXFormula =

VAR Demo =
OR (
Event[Subject] IN { "Demo", "Presentation", "Webex" },
Event[Type] IN { "Demo", "Presentation", "Webex" }
)
VAR Call =
AND (
NOT Event[Subject] IN { "email", "demo", "visit", "presentation", "webex" },
Event[Type] = "call"
)
VAR Email = Event[Subject] IN { "email", "message sent" }
RETURN
SWITCH ( TRUE (), Demo, "DEMO", Call, "CALL", Email, "EMAIL" )

 

Highlighted
Super User III
Super User III

Re: Multiple search criteria consisting of "AND" "OR"

@data23f

 

Did you add it as a calculated column?

Could you share the file or screenshot from dataview like image below?

dataview.png

Try my new Power BI game Cross the River
Highlighted
Helper I
Helper I

Re: Multiple search criteria consisting of "AND" "OR"

screenshot.PNGscreenshot2.PNG

 

Yes, I added as a New Column.

Highlighted
Super User III
Super User III

Re: Multiple search criteria consisting of "AND" "OR"

@data23f

 

Seems Call is a reserved word

 

Try this instead

 

DAXFormula = 
VAR Demo =
OR (
Event[Subject] IN { "Demo", "Presentation", "Webex" },
Event[Type] IN { "Demo", "Presentation", "Webex" }
)
VAR Call_ =
AND (
NOT Event[Subject] IN { "email", "demo", "visit", "presentation", "webex" },
Event[Type] = "call"
)
VAR Email = Event[Subject] IN { "email", "message sent" }
RETURN
SWITCH ( TRUE (), Demo, "DEMO", Call_, "CALL", Email, "EMAIL" )
Try my new Power BI game Cross the River
Highlighted
Helper I
Helper I

Re: Multiple search criteria consisting of "AND" "OR"

I changed to Call_ but still not working.

Also the results that I am expecting is not aligning with the Type and Subject.

 

If I use DAXFormula and insert in table with Type and Subject, then for Type = "Training - Webex", then DAXFormula should be "Demo".


So I think there is some issues still with this formula. Can you look into this?

Also should Search be used?

screenshot3.PNG

Highlighted
Community Support
Community Support

Re: Multiple search criteria consisting of "AND" "OR"

@data23f,

 

You may use SEARCH.

https://community.powerbi.com/t5/Desktop/Test-IF-string-exists-based-on-a-list-of-values/m-p/554637#...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Multiple search criteria consisting of "AND" "OR"

Still haven't received answer.


Why is it so difficult just to find a specific text that displays in multiple fields?

Highlighted
Super User III
Super User III

Re: Multiple search criteria consisting of "AND" "OR"

@data23f, it might be a good idea for you to read this:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

If you could post a subset of the values you have in Subject and Type, and in format that can be copied rather than an image, it will be much easier to help you.

 

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors