Forum Discussion
What-If Parameter with Dynamic Range
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?
Thank you all in advance, any guidance is much appreciated.
Hello, I wanted to let everyone know I found a solution to this problem by following the steps outlined in this post:
https://www.sqlbi.com/articles/how-to-enable-the-single-value-option-in-a-power-bi-slicer/
If one has used Tabular Editor, which I have, the solution is incredibly simple. All you have to do is edit an extended property on the numeric column you wish to use. Any numeric column can be used in a single-value slicer, and it will be responsive to filters as any other field would.
For my solution, I had to add a few steps to (create multiplier values, crossjoin) to get a range of values. A bit of extra work, but the solution fits my needs perfectly.
Hopefully this will help someone.
21 Replies
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.
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.
What would be the desired sampling rate of the values for Sacramento?
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.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
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.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.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.
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!
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!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.
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!
Hello, I wanted to let everyone know I found a solution to this problem by following the steps outlined in this post:
https://www.sqlbi.com/articles/how-to-enable-the-single-value-option-in-a-power-bi-slicer/
If one has used Tabular Editor, which I have, the solution is incredibly simple. All you have to do is edit an extended property on the numeric column you wish to use. Any numeric column can be used in a single-value slicer, and it will be responsive to filters as any other field would.
For my solution, I had to add a few steps to (create multiplier values, crossjoin) to get a range of values. A bit of extra work, but the solution fits my needs perfectly.
Hopefully this will help someone.
