Forum Discussion

arvin's avatar
arvin
Regular Visitor
4 years ago

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. ...
DAX
Filters
slicers
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • arvin's avatar
    arvin
    Regular Visitor
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

      • arvin's avatar
        arvin
        Regular Visitor
        4 years ago

        Hi Anonymous 

        So, I have an excel dataset of housing data. In my report i have a matrix, a card visual and graphs.

        As for now, i use the "room" column in a slicer to filter the visuals on room.

        An apartment can have 1 room or 15 rooms, and everything between.

         

        The stakeholder wants to filter the visuals with a "From Room" and "To Room" dropdrown filter.

         

        I want the reuslt to be like this:  if i choose From = 2 and To = 5, i will see housing data from 2-5 room apartments.

         

        This is the "room" column:

         

         

        I have a card visual, a matrix and a graph. I want all the visuals to adjust when i select for example rooms between 2-5(see picture below).