Multiple Filters with OR Condition

[MattKarriker2]

Good afternoon Forum

I am working on an inventory model and my users are looking for a way to supply multiple filters to visuals with an OR condition.  Example: apply a filter where [On Hand Qty] > 500 or [Stock Room Qty] < 100.  I need the solution to be dynamic in that I do not know all the combinations of attributes or measures a user may want to apply.  Any thoughts on how to apply this to a visual?

[Anonymous]

Hi @MattKarriker2 ,

Here's my sample data.

1.Create a separate table which will be used in the slicer. It is created by entering data.

2.Create the measure.

Measure = 
IF (
    ISFILTERED ( 'Table (2)'[Slicer] ),
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Table (2)'[Slicer] ) = "[On Hand Qty] > 500"
            && CALCULATE (
                SUM ( 'Table'[On Hand Qty] ),
                FILTER ( 'Table', [On Hand Qty] > 500 )
            )
                = MAX ( 'Table'[On Hand Qty] ), 1,
        SELECTEDVALUE ( 'Table (2)'[Slicer] ) = "[Stock Room Qty] < 100"
            && CALCULATE (
                SUM ( 'Table'[Stock Room Qty] ),
                FILTER ( 'Table', [Stock Room Qty] < 100 )
            )
                = MAX ( 'Table'[Stock Room Qty] ), 1
    ),
    1
)

3.Put the measure into Filters, then set Show items when the value is 1.

The result is as follows.

You can check more details from here.

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.

[MattKarriker2]

@Anonymous I am looking for a more dynamic way of accomplishing this.  We are trying to create an Ad-Hoc reporting site using Power BI and I do not know the values or metrics the user will apply to the report.  Any thoughts on this?  

[PaulDBrown]

@MattKarriker2 

If you don't know what is going to be filtered, perhaps the best option is to show them (video, tutorial, whatever)  how to use the filter pane where they can filter by fields (columns) or measures etc...

[PaulDBrown]

@MattKarriker2 

it is possible but you will need to define the criteria (combinations) using either measures (so pre-calculated), or  predefined tables of values (which can be based either a fixed set of values or on values within the model: for example creating a table with increments of "100" between a minimum and maximum value in the model).

Would either of these work for you?

Do you have a dummy dataset to play around with? (to share, upload to a cloud service such as Onedrive, Google Drive, Dropbox etc and share from there)