How to create one silcer for multiple 2 columns

[o59393]

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!

[mahoneypat · Accepted solution]

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]

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]

Yes. Measures can only be used in visual filters

Pat

[jthomson]

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]

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]

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]

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]

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

[o59393]

Hi @mahoneypat 

I have the parameter:

The second step I guess is create the dax measure you stated before. I tried:

How can I get it right? and how should I use this measure with the parameter created?

Thanks!

[mahoneypat]

Instead of using [Parameter Value], use Parameter[Parameter].  You want to take the MIN and MAX of the column, not use the measure.

Regards,

Pat

[o59393]

Hi @mahoneypat 

Can you please take a look at the pbix? I did the dax measure but not sure I understand the concept. I apply the filters with the parameter but the table doesn't update.

Also a question I have is why I should I use an average in that dax measure?

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

Thank you for your help!