Complex Filter Logic Using "AND" and "OR"

[Anonymous]

Very new to Power BI. Please explain throughly and with examples if possible.

Page/Visualtion filters in the filter pane only allow for me to use "And" logic between filters. How do I filter a field using multiple filters from a table (or two tables)? Do I need to create a measure? Or will I have to implement something in the data at the source (SalesForce)?

Here is the structure of the SalesForce report I am trying to replicate:

Filtered By:((((1 OR 2 OR 3) AND 4 AND 5 AND NOT (6 AND 7 AND 8)) AND 9)) 

1. Sales Rep - Product A: Full Name equals John Smith
2. Sales Rep – Product B: Full Name equals John Smith
3. Sales Rep – Product C: Full Name equals John Smith
4. Annual Recurring Revenue greater or equal "USD 17,000"
5. Open Tasks equals 0
6. # Product A Assets greater or equal 1
7. # Product B Assets greater or equal 1
8. # Product C Assets greater or equal 1
9. HealthScore - Overall Score greater or equal 5

[sravikrishnan]

Hi I am very new to power BI. I have read the previous example and have a similar problem. Was hoping for 2 things

1. Someone would send me the code to use.

2. Guide me on where and how to use it (maybe screenshots)

I have about 5 source fields. All the fields are drop down with multiple values

I need a way to say show me all the records where

source field A = ecommerce or SF OR source fieldf B = ecommerce or Sf or Twine  OR source field C = ecommerce or sf or twine or ABC

ie. each field has a choice of several values and there are several such values.

Thanks in advance

@v-lili6-msft @smpa01 @PaulDBrown 

[smpa01]

A sample data source with clearly laid out end result would help.

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

I would sugget you create a new calculated column for conditional(1 OR 2 OR 3)

FLAG=IF([Sales Rep - Product A] = "John Smith"
            || [Sales Rep - Product ] = "John Smith"
            || [Sales Rep - Product C] = "John Smith" ,1,0)

Then use this column in Page/Visualtion filters to set filter is '1'.

Regards,

Lin

[smpa01]

Let's suppose the following is your data source

Sales Rep - Product A	Sales Rep – Product	Sales Rep – Product C	Annual Recurring Revenue	Open Tasks	Product A Assets	Product B Asset	Product C Assets	HealthScore
John Smith	John Smith	John Smith	18000	0	-1	-1	-1	6

Instead of relying on the filter pane you can do following to apply filter

Table 2 =
VAR _1 =
    FILTER (
        'Table',
        [Sales Rep - Product A] = "John Smith"
            || [Sales Rep - Product ] = "John Smith"
            || [Sales Rep - Product C] = "John Smith"
    )
VAR _2 =
    FILTER ( _1, [Annual Recurring Revenue] >= 17000 && [Open Tasks] = 0 )
VAR _3 =
    FILTER (
        _2,
        [Product A Assets] < 1
            && [Product B Asset] < 1
            && [Product C Assets] < 1
    )
VAR _4 =
    FILTER ( _3, [HealthScore] >= 5 )
RETURN
    _4

 Then you can create your measure based on this

sample = 
VAR _1 =
    FILTER (
        'Table',
        [Sales Rep - Product A] = "John Smith"
            || [Sales Rep – Product B] = "John Smith"
            || [Sales Rep – Product C] = "John Smith"
    )
VAR _2 =
    FILTER ( _1, [Annual Recurring Revenue] >= 17000 && [Open Tasks] = 0 )
VAR _3 =
    FILTER (
        _2,
        [Product A Assets] < 1
            && [Product B Asset] < 1
            && [Product C Assets] < 1
    )
VAR _4 =
    FILTER ( _3, [HealthScore] >= 5 )
VAR _5 = CALCULATE(SUMX(_4, [HealthScore]))
RETURN _5

[PaulDBrown]

@Anonymous 

I guess all these fields are within the same table? if so, can you please post an example of the table in data form (not an image).
What will be the filter context(s) you will be using in your visual (date?, rep name?...)

And, what is the final calculation which are seeking?

Edit: you will be able to reduce the complexity by using the filter pane: for example, to limit the rep to John Smith, or including measures and establish the range of values you need for each

[Anonymous]

@PaulDBrown 

All of those fields are within the same table (except "Open Tasks"). If I have to remove that filter, that is acceptable for now. I cannot post an example of the table for information security reasons (also it has an absurd amount of columns).

The filter context would include several other various fields within the table, which is why I was hoping to have it be page level. Potentially, I could create several different measures using the same parameters though?

I am not sure I could use page level filerst on anything other than #4 and #5 possibly. I can't just assign John Smith as the rep because there #1, #2, and #3 are all different fields and may all have different reps assigned, hence the use of OR.