Filtering with an OR condition and user-defined thresholds

[tzuchiao]

Hello community,

I have a dataset like this:
ID, Consistent, Deviation.

I want to remove rows where Consistent < 15 and Deviation > 90, which is easy with a new column:
is_bad = if (Consistent < 15 AND Deviation > 90, True(), False()), then have a filter that keep only is_bad = False().

However, I want the user of the dashboard to be able to change the 15 and 90 values.
I cannot use two filters to keep Consistent >= 15 AND Deviation <= 90, because I want
Consistent >= 15 OR Deviation <= 90.
I also cannot use the calculated column as before since it does not respond to parameters. 
What should I do?

[danextian · Accepted solution]

Hi @tzuchiao 

You can use what-if parameters as the values to feed to the filter. You can modify its calcualted table formula to reference the max consistent/deviation value so the series of numbers becomes dynamic. This feature generates a series of numbers and which slicer allows for a single value selection using a slider. Please see the attached sample pbix.

You can then use a measure to visually filter your table using an OR logic.

Consistent Deviation Filter = 
// This measure calculates the count of rows in 'Table' based on a filter condition.
CALCULATE (
    COUNTROWS ( 'Table' ),
    // Counts the rows in 'Table'
    KEEPFILTERS (
        FILTER (
            // Applies a filter on 'Table'[Consistent] and 'Table'[Deviation] columns
            ALL (
                'Table'[Consistent],
                'Table'[Deviation]
            ),
            // Filters rows where 'Consistent' is greater than or equal to [ConsistentFilter Value]
            // OR 'Deviation' is less than or equal to [DeviationFilter Value]
            'Table'[Consistent] >= [ConsistentFilter Value]
                || 'Table'[Deviation] <= [DeviationFilter Value]
        )
    )
)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result there.

[amitchandak]

@tzuchiao , You have pust this a measures whenever you need 

example

Sumx(filter( Table, [Consistent] >= 15 OR [Deviation] <= 90), Table[Value])