Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

How to create one silcer for multiple 2 columns

Hi all   I would like to have a "between" slicer that contains two columns (min velocity, max velocity).   What I did in order to try to solve this, was creating a new table:   Table = DISTIN...
Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Please use this variation instead.  It returns 1 or 0, and can be added to the Filters on this visual and set to 1.  Note that it requires that the min velocity is above the lower end of the filter and max is below the upper end.

     

     

    IsInRange = if(AND(AVERAGE(Equipment_database[MinVelocity])>=MIN(Parameter[Parameter]), AVERAGE(Equipment_database[MaxVelocity])<=MAX(Parameter[Parameter])),1,0)
     
    Regards,
    Pat

     

jthomson's avatar
jthomson
Icon for Solution Sage rankSolution Sage
5 years ago

What exactly is the expected behaviour of the slicer? If I pick a minimum of 10 and a maximum of 20, and we've got data of:

 

5,9

9,13

13,17

17,21

21,25

 

where these are the minimum and maximum values of your data, which lines should it pick up?

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
5 years ago

Hi jthomson 

 

Let me do an example. If I tell the slicer, filter from 18,000 (min) to 27,000 (max)

 

Then it should only filter me rows 1,2, 4 and 5:

 

 

The 3rd row has speeds of 2000 and 1212 (out of range) therefore shouldnt be filtered.

 

Let me know if it's clear.


Thanks!

 

 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    You don't need your union table for that.  You can just use a What-If Parameter or make a DAX table with

    SlicerValues = GENERATESERIES(0, 30000, 1000)

     

    Use it in your slicer and you can then make a measure like this

    IsInRange = OR([AverageMinVelocity]>=MIN(SlicerValues[Value]), [AverageMaxVelocity]<=MAX(SlicerValues[Value])

     

    It will return true or false and you can use it on the Filter panel as a filter on one or more of your columns in the visual (column name isn't shown in your pic).

     

    Regards,

    Pat

    • o59393's avatar
      o59393
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi mahoneypat the problem is that the parameter will have fixed limites defined at the very start.

       

      The advantage of the table is that it will be dynamic and therefore updated regardless of the min and max entered.

       

      Would it be possible to create a dax measure to have what i'm looking for with the new table?


      Thanks!

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        5 years ago

        Yes.  You could use MIN and MAX as well like

         

        SlicerValues = GENERATESERIES(MIN(Equipment_database[MinVelocity]), MAX(Equipment_database[MaxVelocity]), 1)

         

        If you want to increment by more than 1, you could use the ROUND(MIN(...), -2) to get each value to the closest 100.

         

        Regards,

        Pat