Non-linear parameter
I feel like parameters in Power BI are way under utilized! I’m not talking about the ones in Power Query now but rather the ones that used to be named “What-if”, before field parameters were added.
Parameters are a great way to allow your report users the ability to simulate outcomes! I have a couple of examples here on the blog if you want to have a look (after reading this one).
Here’s the thing though. Sometimes you want a really large span of values and that will generate an equally large number of values in your table.
Let’s have a look, shall we. First of, I’ll create a new parameter
I want to start from 1 and go up to 10 000 with increments of 1, essentially creating a table with 10 000 rows.
If we have a look at the table that was just generated for this, it looks like this:
Sure enough, we have 10 000 rows.
The issue with this is that we can’t fit 10 000 selectable values on a slicer. When trying, Power BI will sample the data. We want to move the slider 1 value higher but the slicer will actually increase by 41, for example.
The other issue is that I’m only at 10 000! That’s a really low number! What if I’m giving you the ability to simulate the cost of, let’s say, watches. A watch can be bought second hand for a $1 or it could be a super, mega high end luxury watch for $100 000. If we want to simulate the watch cost in our report, we would no longer have a mere 10 000 rows but 10 times that!
The thing is, the higher the value, the less you care about granularity. If the watch costs $2 or $8 it’s a huge difference, right! It’s a increase of 300%!
But if the watch costs $96 000 or $96 008, you don’t really care. This is an increase of 0.008%.
Introducing the solution!!! Non-linear parameter values!
To solve this, you can change the “Generateseries” formula to something that will have different increments for different spans and then append them with the union function.
Let’s for example do it like this:
The first 19 values will increase by 1, then we start on 20 and go to 95, increasing by 5 each step. After that we go from 100 up to 980 by increasing each step by 20 and so on.
This entire table will only be 229 rows, yet it covers a span from 1 up to 500 000!
The slider can easily handle 229 values so everything is selectable. I’m able to be very granular in the beginning, for example selecting 7.
I can select 90, 95 or 100. Do we typically really need to be more specific? If yes, increase the values by, for example, increments of 2 instead of 5.
Since the value is growing exponentially, you have to be cautious about the slider. Look more at the value selected than the position of the slider. Note the difference between the once above from 7 to 95. If I put the slicer in the middle, I get 4 000. If I then move the slicer to the right, roughly the same amount as I did between 7-95 I now leap from 4 000 up to 6 600.
Which makes sense. After all, there’s 4 000 values to the left of the selection but 496 000 values represented on the right. Sliding it some more and we’re up to 45 000!
The advantage of this approach is, as I’ve described above, that you simply care less about the granularity the higher the value and with this you can squeeze in a massive amount of values in the same slicer.
Let’s say you’re simulating number of employees. If the company is less than 20 people, you want to select the exact value. If there are roughly 75 000 employees, setting the value to the exact and correct one would be close to impossible since it’s likely changed within a couple of ours due to new hires and people leaving.
If you’re buying a new car for around $5 000, you probably don’t care if it’s $5 001 or even $5 050. You next span would probably be at least an increase of $100.
Here are some of my other posts that mentions or uses parameters 😉 Feel free to be inspired for your next Power BI project!
About The Author
