Forum Discussion

tzuchiao's avatar
tzuchiao
Regular Visitor
1 year ago
Solved

Filtering with an OR condition and user-defined thresholds

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?

Need Help
  • danextian's avatar
    danextian
    1 year ago

    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]
        )
    )
)

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    tzuchiao , You have pust this a measures whenever you need 

     

    example

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

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

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