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.
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.
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.
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.
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?