Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

"either or " logic in filter

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,

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.