Forum Discussion
What-If Parameter with Dynamic Range
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.
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?
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...
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.