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<=MaxXMinY<=InputY<=MaxYBut 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 NameMin XMax XMin YMax YA12.20002.40001.35001.7000A22.20002.40001.35001.7000A32.50002.55001.85001.9000A42.50002.55001.85001.9000A51.60002.50001.07001.9000A61.48001.67000.89001.1000A72.14002.85001.32001.7000 If my input is:X=2.3Y=1.4A1, 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

This can be done with parameters and dax.first create two parameters. I've recreated your example table. I named my parameters Pm_x_inputpm_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.

Forum Discussion

Aidan12345

Frequent Visitor

1 year ago

Solved

Filter that filters between two columns

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

Need Help

Tutorial Requests

ChielFaber
1 year ago
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
1 year ago
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))

4 Replies

bhanu_gautam
Super User
1 year ago
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.
ChielFaber
Super User
1 year ago
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
  Frequent Visitor
  1 year ago
  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))

danextian

Super User

1 year ago

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.

min max input.pbix33 KB

Forum Discussion

Filter that filters between two columns

4 Replies

Related content

Slicer for filtering several columns

Apply data to all rows in a filtered column

odata Report Level Filters Multiple Filters Same Column

Adding filters to a calculated column

Filter