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 = 
DISTINCT(
UNION(
VALUES(Equipment_database[MinVelocity]),
VALUES(Equipment_database[MaxVelocity])
))

 

The table correctly has all the values from both columns (max and min)

 

How can I enable this new slicer called "velocities" to filter all by tables?

 

 

pbix:  https://1drv.ms/u/s!ApgeWwGTKtFdhzdufgkRUs0RVGxd?e=v0SMtI

 

Thanks all!

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

     

11 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    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

     

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

      Awesome mahoneypat 

       

      Almost there. Just one question, how can I make that measure called "IsInRange" filter all the page ?

       

      I drag the measure to the "filters on this page" but wont work. It is possible only to drag the measure to each visual created?

       

      Thanks!

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

        Yes. Measures can only be used in visual filters

        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