Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Complex Filter Logic Using "AND" and "OR"

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 fie...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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's avatar
smpa01
Icon for Community Champion rankCommunity Champion
6 years ago

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