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

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

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

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

 

Super User
Super User

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

data23f Frequent Visitor
Frequent Visitor

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

screenshot.PNGscreenshot2.PNG

 

Yes, I added as a New Column.

Super User
Super User

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

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

Community Support Team
Community Support Team

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.
data23f Frequent Visitor
Frequent Visitor

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?

sturlaws Regular Visitor
Regular Visitor

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.