"either or " logic in filter

[Anonymous]

Hi 

I am new in PowerBI, currently I am building a report. I have two columns in one table, one column projects have environmental impact: yes,no, blank; another column is multiple selections, but I only need three type of the slections for sustainable projects. 

How do I apply the filter, as long as people fulfilled one of the column, I will be able to selete them.

In PowerBI filter, when there are multiple filters, they can be evaluated by using the AND (&&) , meaning all conditions must be TRUE, so they have to filled in both column, however, I want them as long as filled in one of them(environmental impact or different sustainable projects), they will be filtered in my dashboard.(either or logic)

How do I be able to do that?

thank you so much!

Best regards,

[v-stephen-msft]

Hi @Anonymous ,

You want to add a slicer, and when you check the value of the slicer, it's to exclude, not include, right?

If so, it is recommended that you create a separate table to place your slicer's columns, and then create a measure filter to exclude the results that your slicer selects.

Or reference:

Power BI Exclude data based on Slicer selection - Ben's Blog

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Stephen, 

Thanks for answering, I don't think it is exclude. Normally we apply filter, one filter added and we add another one, the filter is based on the previous one, narrow down the scale. However, how do I apply filter which is in a equal stutus, if the people selected in either one of the column, then don't need to select in another column.

For example, if people select sustainable level zero impact, even though they did not fill up in environmental impact column(leave as a blank), and I will be able to filter out sustainable projects. Current filter is if they select sustainable level and did not filled out environmental impact yes/no, then this project will not be selected. 

I hope it explains my question.

Best regards,

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Sorry for my bad understanding.

You want two slicers, and between the slicers is OR logic instead of AND logic, right?

The solution is to create two seperate tables for two slicers and create a measure as the visual-level filter.

Sample data

Table 2 = DISTINCT('Table'[environmental impact])

Table 3 = DISTINCT('Table'[selection])

Measure is created as

Measure =
IF (
    (
        ISFILTERED ( 'Table 2'[environmental impact] )
            && MAX ( 'Table'[environmental impact] )
                IN ALLSELECTED ( 'Table 2'[environmental impact] )
    )
        || (
            ISFILTERED ( 'Table 3'[selection] )
                && MAX ( 'Table'[selection] ) IN ALLSELECTED ( 'Table 3'[selection] )
        ),
    1
)

Create a visual with the following fields, put the measure into the filters. Set up show items when the values is 1.

Environmental impact is from Table 2. Selection is from Table 3.

Here's the result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.