What-If Parameter with Dynamic Range

[pbi_dot_edu]

I have a requirement to build a single-value slicer that is responsive to filter context. I have achieved the single-value slicer through a what-if parameter, but it will not respond to filters. I started with this code:

Cost Parameter = GENERATESERIES(
MIN('Table'[Minimum Cost]),
MAX('Table'[Maximum Cost]),
1)

And it gives me this result:

The range in the slider is currently the range of the total dataset, so $45-$8000. This will not work, especially with sampling. The range should be responsive to the min and max of the selected city, so when I choose Austin, I would like it to be $45-$75, when I choose Sacramento it should be $3000-$8000, and so on.

Things I've tried:

- The obvious step of making sure the slicers are interacting with the parameter visual. It says they are, but the values are unaffected.

- Adding various filter statements directly into the min and max fields. Again, the values are unaffected.

- Using a measure in the GENERATESERIES statement, something like: 

Min Cost =
CALCULATE(
MIN('Table'[Minimum Cost]),
'Table'[City] = SELECTEDVALUE('Table'[City])
)

This gives me an error that "The arguments in the GENERATESERIES function cannot be blank", which makes no sense as there aren't any blank rows.

Is there any way to achieve this dynamic selection range?

Here is some Sample Data 

Thank you all in advance, any guidance is much appreciated.

[v-karpurapud]

Hi @pbi_dot_edu 

We have not received a response from you regarding the query and were following up to check if you have found a resolution from the last response. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank You!

[pbi_dot_edu]

Hi @v-karpurapud ,

Apologies for the late reply; we had moved on to another project and I have just now been able to return to this to test the solution.

Unfortunately, this does not work for me, as the output is a range slider. I need the output to be a single value, as this value is an input in another calculation. When the calculation receives a range, it does not work.

I appreciate your help with this, and will continue to search for a solution.

[v-karpurapud]

Hi  @pbi_dot_edu 

Thank you for the update, and no worries at all regarding the delay.

Considering that your calculation requires a single value input and the current approach results in a range slider, it appears the requirement is more specific than what standard configuration options support. Since this involves behavior that may depend on internal Power BI logic or limitations, I recommend reaching out to Microsoft Support for a deeper investigation.

You can submit a ticket through the Microsoft Power BI Support Portal:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank You!

[v-karpurapud]

Hi @pbi_dot_edu 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[pbi_dot_edu]

Hello, my issue is not resolved, so I do not feel comfortable accepting a solution. I attempted to create a disconnected table and use it in the slicer but it did not yield the desired results. I've provided sample data in the original post so if someone could reproduce the suggested solution I'd be very grateful, but as of now there is no resolution.

[v-karpurapud]

Hi @pbi_dot_edu 

I have attached a few screenshots and the .pbix file demonstrating this logic. Please review them to see if this solution aligns with your requirements.

When no city selected it should display all  the range of the total dataset is $45-$8000.

When Austin is selected, it should display the range of the total dataset is between $45-$75.

When Sacramento is selected, it should display the range of the total dataset is between $3000-$8000.

 I hope this the required solution you are expecting.

If this response is helpful, please mark it as an Accepted Solution to benefit other community members.
 

Thank You!

[v-karpurapud]

Hi @pbi_dot_edu 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @pbi_dot_edu 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you

[v-karpurapud]

Hi @pbi_dot_edu 

Thank you for reaching out to the Microsoft Fabric Community Forum regarding your issue.

The best approach to achieve your requirement is by using a disconnected table as suggested by @lbendlin , as What-If parameters are static and do not respond dynamically to filter context. The key challenge here is that GENERATESERIES() does not accept dynamic min/max values, making it unsuitable for creating a dynamically adjusting range.

To achieve the desired functionality,

1. Define a table that contains a reasonable range of cost values. This will act as the source for the slicer.

2. Develop a measure that dynamically calculates the min and max values for the selected city and filters the            slicer accordingly.
3. Use the measure to ensure that only relevant values within the selected city’s range are displayed in the slicer.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank You.

[lbendlin]

Your What-If parameter can have a maximum of 1000 discrete values. There is no way around forced sampling.

Use a filter on the filter pane instead.

[pbi_dot_edu]

Hi, apologies if my question was unclear. I am not looking for a way around forced sampling. I am looking for a way to filter the range so that the user will find the parameter more helpful. A user from Austin will find data up to 8000 cumbersome and unnecessary.

The end result will be published to the web, so no option for a filter pane. 

[lbendlin]

What would be the desired sampling rate of the values for Sacramento?

[pbi_dot_edu]

The bounds of the range should be the minimum and maximum for the chosen city. For Sacramento, the range should be 3000-8000.

Because a wider range of values is expected, less granularity is not an issue. If user inputs 5050 and it defaults to 5075, this is not an issue. If Austin user inputs 50 and it defaults to 75, that is an issue. Again, it is about how the range appears to the user.

Obviously this is dummy data, so we are not speaking about cities...

[lbendlin]

You would need to calculate the sampling rate for each scenario. In the case of Sacramento the sampling rate would have to be 6 or higher, as a sampling rate of 5 would result in 1001 values.

[pbi_dot_edu]

I appreciate the advice about sampling, but again, I am more concerned about the bounds of the range. A slider from 45-8000 is unusable for a user from Austin regardless of sampling.

[lbendlin]

You would need to abandon the idea of a What-If parameter, and instead prepare a disconnected table that has the entire range of values, from the lowest minumum to the highest maximum.  This has to be a calculated table, much as the What-if parameter.

Then you would have to use that for a dropdown slicer.  That slicer would need to have a measure filter. The measure being "is within the min/max selected range".

Lastly you would have to figure out how to make the user SELECT Austin (for example), one option would be to educate your users how to click the required table row, another option would be to have a single select City slicer.

This will make for a , uhm, suboptimal UX at best.

[pbi_dot_edu]

I have a single select City slicer. My hope was the slicer would simply filter the parameter, but this seems to not work. I will try the disconnected table idea.

Surely this cannot be an unusual request?