topic Re: Numeric range dropdown filter in DAX Commands and Tips

Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 14:35:41 GMT

Hi

I have an excel dataset as underlying data. It is housing data, so for each row i have a column saying how many rooms the apartment have. See the image below for the column and values.

The stakeholders want to have two filters in the report. One saying "Room from" and the other "Room to". So basically they want a numeric range slicer, but as a dropdown list filter, something like the image below:

The problem is that if i choose From = 2 and To = 5 it does not filter the data from 2 TO 5, it only shows data with room = 2 AND room = 5, as expected. I want the data to show the range between the to options.

I believe there must be a work around on this issue with nested if statements or a function?

I tried to solve it by creating two seperate tables with unique values containing all possible rooms(picture below), one "from" and one "to". My next idea was to find a function saying when choosing 2 from the FROM table/filter and 5 from the TO table/slicer, the data filters everyting from 2 to 5 room apartments.

Has anyone faced this issue before and can you guys help me out with this?

Re: Numeric range dropdown filter

smpa01 — Mon, 17 Jan 2022 14:45:21 GMT

arvin To achieve a between behaviour, you need to use all the (From, To) values from the same table and they can't come from two different tables.

Re: Numeric range dropdown filter

ValtteriN — Mon, 17 Jan 2022 15:02:33 GMT

Hi,

Here is one way to do this:

First I created to parameter for slicing the values. Then I created a filter measure:

RoomsFilter =

var _rooms = max(RoomsToFrom[Rooms]) return

IF(and(_rooms<=[RoomsTo Value],_rooms>=[RoomsFrom Value]) ,1,0)

Now I just applied Filter measure to my table:

By using these steps the behaviour you described can be achieved.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 15:08:29 GMT

Hi, I did ecatly what you said and tried to apply the measure in my matrix, but it doesnt seem to work, it worked on tables, but i have multiple matrixes like the image below and nothing happens:

Re: Numeric range dropdown filter

ValtteriN — Mon, 17 Jan 2022 15:13:47 GMT

Hi,

It is important the filter is "Rooms filter is 1" Now you have "Rooms filter is 1 or 0" This defeats the purpose and it is likely the reason the filter is not working.

Re: Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 15:18:35 GMT

Sorry i sent the wrong printscreen. Here is the correct one. I have the measure = 1 and nothing shows.

Re: Numeric range dropdown filter

ValtteriN — Mon, 17 Jan 2022 15:22:21 GMT

Hmm,

That is curious behaviour. Could you show the DAX you used?

Re: Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 15:28:45 GMT

I created two parameters; Från(from) and Till(to)

The measure is below:

-Blad1[antal rum] is the column with rooms in my dataset.

RoomsFilter =

var _rooms = max(Blad1[antal rum])
return

IF(and(_rooms<=[Till Value],_rooms>=[Från Value]) ,1,0)

I do have a measure in the matrix Values section, i dont know if that creates a problem.

Re: Numeric range dropdown filter

ValtteriN — Mon, 17 Jan 2022 15:38:45 GMT

The filter measure seems to be correct. It is entirely possible that your other measure in matrix values causes the issue. Here is an example of that:

Impossible measure = CALCULATE(SUM(RoomsToFrom[Rooms]),RoomsToFrom[Rooms]=1)

To solve this you can remove the filter context from this measure, since I don't know what your measure does I can only suggest using ALL.
E.g.

Impossible measure2 = CALCULATE(SUM(RoomsToFrom[Rooms]),RoomsToFrom[Rooms]=1,ALL(RoomsToFrom))

There are better ways to do this, but I would need to know what your other measure does.

Re: Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 15:39:55 GMT

It works on tables and graphs, but when i have no option selected in the parameters the data disapears, can i create the measure so that when nothing is selected, all data is showing?

It does not work on matrixes and cards, in the picture below i can't select the Rooms Filter = 1.

Re: Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 15:50:06 GMT

This is a example of the card measure:

Card Measure =

CALCULATE(COUNT(Blad1[kontraktspris]),Blad1[bostads-kategori] = "B")

This is the measure that I use in the matrix:

Matrix measure =

CALCULATE(AVERAGE(Blad1[kontraktspris]),Blad1[bostads-kategori] = "V")

Re: Numeric range dropdown filter

ValtteriN — Mon, 17 Jan 2022 16:04:37 GMT

Here is modified filter which works also when nothing is selected:

RoomsFilter =

var _rooms = max(RoomsToFrom[Rooms])

var _roomsfrom = IF([RoomsFrom Value]=BLANK(),0,[RoomsFrom Value])

var _roomsto = IF([RoomsTo Value]=BLANK(),999,[RoomsTo Value])

return

IF(and(_rooms<=_roomsto,_rooms>=_roomsfrom) ,1,0)

Re: Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 17:01:12 GMT

Hi ValtteriN

Below you can see the calculated columns and measures that i use as cards and matrixes. The graph works perfectly with the parameter but the matrix doesnt show any data and the card and can't set the Rooms Filter to = 1.

Graph(works)
The graph works fine. I have a date column as axis, a calculated column as Legend and a measure in Values:
-Legend:

Calculated Column=

if(Table1[columnX] = "Legend1", "Legend2",

if(Table1[columnX] <> "Legend1" || Table1[columnX] = "Legend1","All"))
-Values:

Measure =

CALCULATE(AVERAGE(Table1[columnY]),Table1[columnZ] = "B")

I also have a date filter on the filters pane saying only look at data from today and 3 months back.

Card(doesnt work)

Measure =

CALCULATE(COUNT(Table1[ColumnY]),Table1[ColumnZ] = "B")

I also have a date filter on the filters pane saying only look at data from today and 3 months back.

Matrix(doesnt work)

-Rows
Calculated Column =
if(Table1[ColumnX] = "example text", "output",
if(Table1[ColumnX] <> "example text"|| Table1[ColumnX] = "example text", "output2"))

-Columns
Calculated Column =
if(Table1[Datesegment] = "1", " X",
if(Table1[Datesegment] = "2", " Y",
if(Table1[Datesegment] = "3", " Z",
if(Table1[Datesegment] = "4", "F"))))
-Values
Measure =
CALCULATE(AVERAGE(Table1[price]),Table1[category] = "V")

Re: Numeric range dropdown filter

ValtteriN — Mon, 17 Jan 2022 17:21:37 GMT

arvin

Card:

Normally you can't filter card with a measure. To circumvent this we can first create a table visual and place the filter there:

Now convert it to a card:

As for the matrix:

Instead of nested IFs you should use
SWITCH(TRUE(),
condition1,result1,
condition2,resul2) structure.

This doesn't solve the issue but it is more readable.

My assumption is that your columns work and the issue is with the measure. You can you test this b testing the visual with this:

TestMeasure =
CALCULATE(AVERAGE(Table1[price]),Table1[category] = "V",ALL(Table1))

If this return values this should work:

Working Measure =
var cc1 = Max(Calculated Column)
var cc2 = Max(Calculated Column2) return

CALCULATE(AVERAGE(Table1[price]),Table1[category] = "V",ALL(Table1),
Table1[Calculated Column]=cc1,Table1[Calculated Column]=cc2)

Re: Numeric range dropdown filter

arvin — Mon, 17 Jan 2022 23:01:07 GMT

Hi ValtteriN

I tried the card trick and it did not work when choosing a parameter, the value is the same every time.

I also tried the test measure for the matrix, but all values in the matrix became identical and when choosing parameters they all disappeared, picture below:

TestMeasure =

CALCULATE(AVERAGE(Table1[price]),Table1[category] = "V",ALL(Table1))

Re: Numeric range dropdown filter

ValtteriN — Tue, 18 Jan 2022 06:34:07 GMT

Hi,
arvin
The test measure did it job if you got some values into the matrix. Now we just need to figure out how to get the filter context to function based on this test. What values did the "working measrure" return?

Re: Numeric range dropdown filter

arvin — Tue, 18 Jan 2022 11:59:57 GMT

Hi ValtteriN

The matrix now shows correct values with the "working measure" in the values section.
The problem is still that it does not respond accurate to the parameters, when choosing for example from = 2 and to = 4, the data in the matrix disappears.

Re: Numeric range dropdown filter

Anonymous — Thu, 20 Jan 2022 08:40:27 GMT

Hi arvin ,

You will need to create two separate tables as slicer (from and to) and make sure there's no relationship between these two table and the fact table.

Create a measure, let's call it [measure1], to get the number of rooms (which you will use to compare with the number selected in the slicers). You could add it to matrix to check if it gets the correct result then remove from visual.

Then create the second measure.

Measure2 =
var _min = selectedvalue(from_slicer[from])
var _max = selectedvalue(to_slicer[to])
return
IF([measure1]>=_min&&[measure1]<=_max,1,0)

At last, add the measure to visual filter and set value = 1.

If it still doesn't work, please show some sample data and expected result so that we could test the formula.

Best Regards,

Jay

Re: Numeric range dropdown filter

arvin — Fri, 21 Jan 2022 09:53:14 GMT

Hi Anonymous
How do i get the number of rooms for measure 1? VALUES?

Re: Numeric range dropdown filter

Anonymous — Fri, 21 Jan 2022 10:06:43 GMT

Hi arvin ,

That is based on your data and requirement. I don't know what your data looks like and I don't know what result you want. Share the sample data without confidential information and the expected result, so that we could decide to use sum() or max() or min() or other function to get it.

Best Regards,

Jay