Apologies in advance for the long explanation, and many thanks in advance to any who provide a response to it. Suppose I have the following dataset. I can create a slicer using the field "Value" as a filter. This results in a slicer that looks like so.Changing the values at either end of this slicer will dynamically update the original table, filtering it to only include rows where "Value" is within the given range (so X <= Value <= Y). All good so far. Now, say I want to keep one end of this range in place; perhaps I always want the lower end to be 1, but the higher end can be set freely (I'm aware that this can be accomplished by changing the slicer setting from "Between" to "Less than or equal to", but let's say I also don't want to display a vestigial lower bound that can't be changed). My initial thought was to create a parameter that holds the value of this upper bound, and display a slicer that can change that.However, after many hours and many false starts, I can't figure out any way to use this parameter value in the way I need. I have already investigated the following: Creating another calculated column that holds whether "Value" is less than or equal to the parameter value. This doesn't do the job since calculated columns only update when the model updates, and so this column wouldn't dynamically update in response to the parameter value changing.Creating a measure on the original table which is equal to 1 when "Value" is less than or equal to the parameter value, and 0 otherwise, and then using that as a filter whereever I need this behavior. This nearly works, however apparently you can't use a measure to filter an entire page, nor to filter certain visuals, such as cards.The reason this is confusing to me is that I believe I'm trying to achieve something already within PowerBI's capability. Remember: this exact dynamic filtering behavior worked perfectly fine using the slicer I made by dragging the calculated column field into the first slicer directly. All I'm trying to do is replicate whatever PowerBI is doing under the hood with that slicer, but with a parameter that is set by a different slicer. Is there some reason one of these slicers can achieve this behavior, but the other one can't? If so, what's the fundemental difference between them? Is there any way to salvage this solution? If not, is there a recommended alternative? Any insight as to this situation would be greatly appreciated.

An option might be to create a calculation group to work with your parameter. If you create a calculation item like Only chosen values = VAR ChosenValue = [Parameter Value] VAR Result = CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( 'Table'[Value] <= ChosenValue ) ) RETURN Result you could apply that as a page level filter, and then any measures would only be calculated for the desired selection. Any other values would return blank, and so would be filtered out of visuals.

eoinnpm

Regular Visitor

1 year ago

Solved

Replicating Behavior of Range Slicer on Numeric Column using Parameter Slicer

Apologies in advance for the long explanation, and many thanks in advance to any who provide a response to it.

Suppose I have the following dataset.

I can create a slicer using the field "Value" as a filter. This results in a slicer that looks like so.

Changing the values at either end of this slicer will dynamically update the original table, filtering it to only include rows where "Value" is within the given range (so X <= Value <= Y). All good so far.

Now, say I want to keep one end of this range in place; perhaps I always want the lower end to be 1, but the higher end can be set freely (I'm aware that this can be accomplished by changing the slicer setting from "Between" to "Less than or equal to", but let's say I also don't want to display a vestigial lower bound that can't be changed). My initial thought was to create a parameter that holds the value of this upper bound, and display a slicer that can change that.

However, after many hours and many false starts, I can't figure out any way to use this parameter value in the way I need. I have already investigated the following:

Creating another calculated column that holds whether "Value" is less than or equal to the parameter value. This doesn't do the job since calculated columns only update when the model updates, and so this column wouldn't dynamically update in response to the parameter value changing.
Creating a measure on the original table which is equal to 1 when "Value" is less than or equal to the parameter value, and 0 otherwise, and then using that as a filter whereever I need this behavior. This nearly works, however apparently you can't use a measure to filter an entire page, nor to filter certain visuals, such as cards.

The reason this is confusing to me is that I believe I'm trying to achieve something already within PowerBI's capability. Remember: this exact dynamic filtering behavior worked perfectly fine using the slicer I made by dragging the calculated column field into the first slicer directly. All I'm trying to do is replicate whatever PowerBI is doing under the hood with that slicer, but with a parameter that is set by a different slicer.

Is there some reason one of these slicers can achieve this behavior, but the other one can't? If so, what's the fundemental difference between them? Is there any way to salvage this solution? If not, is there a recommended alternative? Any insight as to this situation would be greatly appreciated.

Need Help

johnt75
1 year ago
An option might be to create a calculation group to work with your parameter. If you create a calculation item like

Only chosen values = VAR ChosenValue = [Parameter Value] VAR Result = CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( 'Table'[Value] <= ChosenValue ) ) RETURN Result

you could apply that as a page level filter, and then any measures would only be calculated for the desired selection. Any other values would return blank, and so would be filtered out of visuals.

3 Replies

johnt75
Super User
1 year ago
An option might be to create a calculation group to work with your parameter. If you create a calculation item like

Only chosen values = VAR ChosenValue = [Parameter Value] VAR Result = CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( 'Table'[Value] <= ChosenValue ) ) RETURN Result

you could apply that as a page level filter, and then any measures would only be calculated for the desired selection. Any other values would return blank, and so would be filtered out of visuals.
- eoinnpm
  Regular Visitor
  1 year ago
  This worked a treat. Thank you so much!
Anonymous
Not applicable
1 year ago
Hi eoinnpm

Please try this:

Here's the sample data:

Then add a custom visual named Preselected Slicer:

Create a new calculated table:

Slicer = {TRUE(),FALSE()}

Create a measure:

MEASURE = VAR _Slicer = [Parameter Value] RETURN IF ( SELECTEDVALUE ( 'Table'[Value] ) <= _Slicer, TRUE (), FALSE () )

Finally create a Preselected slicer like this:

You can hide the slicer in the Sync Slicer pane:

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Replicating Behavior of Range Slicer on Numeric Column using Parameter Slicer.pbix242 KB