Forum Discussion
Numeric range dropdown filter
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?
20 Replies
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]) returnIF(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!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:
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.
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
Hi Anonymous
How do i get the number of rooms for measure 1? VALUES?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
