Filter that filters between two columns

[Aidan12345]

I Have four columns that I want to filter between, Min X, Max X, Min Y, Max Y. I would like to have a filter which has a numeric (text box) input where I can input a number and it then shows only the rows that where the number is between the MIN and MAX.

To further explain what I would like would be a two text boxes (one for X and one for Y) the maths behind it should be:

MinX<=InputX<=MaxX

MinY<=InputY<=MaxY

But the problem with this is that I don't know how to do that, I don't know if I will have to make a new measure and what to put in that measure.

Bellow is an example of the table that I have and what an input would be an the expected output.

Box Name	Min X	Max X	Min Y	Max Y
A1	2.2000	2.4000	1.3500	1.7000
A2	2.2000	2.4000	1.3500	1.7000
A3	2.5000	2.5500	1.8500	1.9000
A4	2.5000	2.5500	1.8500	1.9000
A5	1.6000	2.5000	1.0700	1.9000
A6	1.4800	1.6700	0.8900	1.1000
A7	2.1400	2.8500	1.3200	1.7000

If my input is:

X=2.3

Y=1.4

A1, A2, A5, A7 should all stay on the page but this does not happen when I use a between slicer.

Bellow is a page that is similar to what I have tried. i have four slicers that a all between as you cant have a between slicer that works for two columns but this system does not at all.

When I tell the X MIN slicer that its min should be 2.3 and the X MAX slicer 2.3 I get the wrong out put completely even though it should output A1, A2, A5, A7 and more.

I cant just put the inputs to be the same as the values in the table as it will show wrong information along with correct information.

I can provide more information if needed but I only work on two days a week

[ChielFaber · Accepted solution]

This can be done with parameters and dax.

first create two parameters. I've recreated your example table. I named my parameters 

Pm_x_input

pm_y_input

With the parameters you get a slicer but also a text box where you can put in the numbers as with your request.

choose Range between 0 and 5 with 0.1 increments or any increment you like. 

Create 4 dax measures:

max_x = SELECTEDVALUE('table'[Max X])

max_y = SELECTEDVALUE('table'[Max Y])

min_x = SELECTEDVALUE('table'[Min X])

min_y = SELECTEDVALUE('table'[Min Y])

then the most important step to put everything together. Creata a separate dax measure with the following code:

FilterBoxVisibility =

VAR InputX = SELECTEDVALUE('Pm_x_input'[Pm_x_input])

VAR InputY = SELECTEDVALUE('pm_y_input '[pm_y_input ])

VAR IsXInRange =

   _Metingen[min_x] <= InputX &&

    InputX <= _Metingen[max_x]

VAR IsYInRange =

    _Metingen[min_y] <= InputY &&

    InputY <= _Metingen[max_y]

RETURN

    IF(IsXInRange && IsYInRange, 1, 0)

This DAX measure FilterBoxVisibility checks if a given point, defined by the X and Y values entered through the parameters pm_x_input and pm_y_input, falls within the X and Y ranges (min and max) of each row (box).

Then put this FilterBoxVisibility measure into a table visual with the fields. This will show a column with values of 0 and zero. In the filterpane filter the table to only show values where FilterBoxVisibility = 1.

[danextian]

Hi @Aidan12345 

Create a numeric range parameter from the modelling tab. The GUI will not allow you to use decimals but you can adjust the formula later. Do this twice - one for x and one for y.

Then create this measure and use it is a visual filter

Filter2 = 
VAR _InputX = [XMax Value]  -- Assuming you have a measure or parameter for InputX
VAR _InputY = [YMax Value]  -- Assuming you have a measure or parameter for InputY

RETURN
CALCULATE(
    COUNTROWS('Table'),
    KEEPFILTERS(
        'Table'[Min X] <= _InputX && _InputX <= 'Table'[Max X] &&
        'Table'[Min Y] <= _InputY && _InputY <= 'Table'[Max Y]
    )
)

Please see the attached sample pbix.

[ChielFaber · Accepted solution]

This can be done with parameters and dax.

first create two parameters. I've recreated your example table. I named my parameters 

Pm_x_input

pm_y_input

With the parameters you get a slicer but also a text box where you can put in the numbers as with your request.

choose Range between 0 and 5 with 0.1 increments or any increment you like. 

Create 4 dax measures:

max_x = SELECTEDVALUE('table'[Max X])

max_y = SELECTEDVALUE('table'[Max Y])

min_x = SELECTEDVALUE('table'[Min X])

min_y = SELECTEDVALUE('table'[Min Y])

then the most important step to put everything together. Creata a separate dax measure with the following code:

FilterBoxVisibility =

VAR InputX = SELECTEDVALUE('Pm_x_input'[Pm_x_input])

VAR InputY = SELECTEDVALUE('pm_y_input '[pm_y_input ])

VAR IsXInRange =

   _Metingen[min_x] <= InputX &&

    InputX <= _Metingen[max_x]

VAR IsYInRange =

    _Metingen[min_y] <= InputY &&

    InputY <= _Metingen[max_y]

RETURN

    IF(IsXInRange && IsYInRange, 1, 0)

This DAX measure FilterBoxVisibility checks if a given point, defined by the X and Y values entered through the parameters pm_x_input and pm_y_input, falls within the X and Y ranges (min and max) of each row (box).

Then put this FilterBoxVisibility measure into a table visual with the fields. This will show a column with values of 0 and zero. In the filterpane filter the table to only show values where FilterBoxVisibility = 1.

[Aidan12345 · Accepted solution]

I did modify the DAX so that it would not filter everything out if i had blank values for some bits of the table

FilterBoxVisibility =

VAR InputX = SELECTEDVALUE('Pm_x_input'[Pm_x_input])

VAR InputY = SELECTEDVALUE('pm_y_input'[pm_y_input])

VAR IsXInRange =

    NOT(ISBLANK('Table'[min_x])) &&

    NOT(ISBLANK('Table'[max_x])) &&

    'Table'[min_x] <= InputX &&

    InputX <= 'Table'[max_x]

VAR IsYInRange =

    NOT(ISBLANK('Table'[min_y])) &&

    NOT(ISBLANK('Table'[max_y])) &&

    'Table'[min_y] <= InputY &&

    InputY <= 'Table'[max_y]

RETURN

    IF(

        ISBLANK(InputX) || ISBLANK(InputY),

        1,

        IF(IsXInRange && IsYInRange, 1, 0))

[bhanu_gautam]

@Aidan12345 , Try using What if parameter

Go to the Modeling tab and select "New Parameter" -> "What If Parameter".
Create a parameter for X with a range that covers your Min X and Max X values.
Create a parameter for Y with a range that covers your Min Y and Max Y values.

DAX

FilteredRows =

VAR InputX = SELECTEDVALUE('X Parameter'[X Parameter])

VAR InputY = SELECTEDVALUE('Y Parameter'[Y Parameter])

RETURN

IF(

    MAX('YourTable'[Min X]) <= InputX && InputX <= MAX('YourTable'[Max X]) &&

    MAX('YourTable'[Min Y]) <= InputY && InputY <= MAX('YourTable'[Max Y]),

    1,

    0

)

Add the measure to the visual you want to filter.
Set the filter condition to show only rows where the measure equals 1.