This article describes how to create numeric input variables in Power BI. Dates are also covered at the bottom of this article. Text input values are not covered here but would follow a similar pattern by preloading the parameter table with all possible inputs. Field Parameters are a similar technique and may be what you want. Check out my other article here: https://www.antaresanalytics.net/post/measure-dimension-and-switch-parameters-in-power-bi-what-s-the-difference
Creating the User Input Variable
Inserting input variables is a bit unintuitive in Power BI, but hopefully this article will clear it up. There are basically two steps:
1. Create a new "What If" parameter under the Modeling Tab:
Clicking this button will bring up a new window with several input options.
2. Enter the range of numbers and the number of steps in between:
Name: This is the name of your parameter (input variable).
Data Type: This defines the type of variable. Whole number, Decimal number or Fixed decimal number.
Minimum & Maximum: These are the starting and ending numbers for your variable (think of it like a range of valid inputs).
Increment: This represents the increment between your minimum and maximum values. This will affect how many values can be selected as an input.
After you have your range of numbers (values entered for Minimum, Maximum) and the increment, click OK and Power BI will insert a new table for you in your DAX model. This is your table of valid input options for your users.
Example Use Cases for Ranges and Increments
If you chose 0 as your minimum, 2 as your maximum, and 1 as your increment, you will get a table of values for:
0 |
1 |
2 |
If you chose a 0 min, 2 max, and 0.5 as your increment, you will get:
0 |
0.5 |
1.0 |
1.5 |
2.0 |
NOTE: If you do chose a decimal number as your increment, be sure to have a leading zero. Otherwise, Power BI might not let you continue with the parameter. For example, you need to enter 0.5, instead of .5.
Adding the Input Box
If you chose to Add a slicer to the page, it should have also inserted a single value slider filter accordingly.
Alien Tip: Notice how there is only one slider (the little circle handle). This allows us to limit the user input to a single value, as opposed to having 'between' or 'greater than' values. By using a "what if" parameter, we get this functionality. If we used a normal table, we would be unable to get this single value slider. On the other hand, we are unable to get a multiple value slider with a what if parameter. Keep these points in mind.
A Deeper Look
Checking out the DAX formula for the Parameter table, we can see that it's simple a GENERATESERIES() function. This has three variables to pass in; the minimum, maximum, and the iteration. We can easily modify these values to increase or decrease the number of options we want our users to have. At the same time, this creates a great way to automatically validate our users inputs. For example, we don't want them inserting the string 'ten' instead of a numeric '10'.
We are not limited to hard coded values either. If we want a list of values from 1 to the number of records we have in the data set, we can use COUNTA() for the maximum parameter. This will result in a dynamic user input variable. For example:
= GENERATESERIES( 1, COUNTA(MyDataSet[ID]), 1)Question: "Am I limited to just numeric inputs?"
Answer: With a small amount of innovation, we can create other types. Though, we need to apply some extra steps depending on what you are looking for.
How about a date input?
We can accomplish this by using the numeric serial values that all dates use. For example todays date (as of writing) is 9/12/2018. The serial value for this is 43355. Adding 1 to this number gives us tomorrows date, 9/13/2018, or 43356. Knowing this, we can change our input variables for the GENERATESERIES() function to:
= GENERATESERIES( 43355, 43370, 1 )
43370 is the last date of 2018, 12/31/2018. Of course, doing it this way would limit us to a single date. Instead, we can use DAX date functions like TODAY() and DATE() to get a dynamic result:
=GENERATESERIES(
VALUE( TODAY() ),
VALUE( DATE( YEAR( TODAY() ), 12, 31)),
1
)
Why am I using VALUE()? If we don't, the value is returned as a date type value. It may be a bug, but it messes up the single value slicer and ends up putting two input boxes, and effectively breaking the parameter:
So we want to use Value() in our parameter so our slider works.
"But I don't want that number to show on my input box. It looks ugly!"
You're right. Unfortunately we can't manually change the data type either, because it'll break the parameter with the same issues as above. However, if you don't mind your users just using the slider as opposed to the input box, you can 'hide it' by creating a card, filling in the background with white, and lay it over the input box. Something like this:
It may not be the most graceful, but it gets the job done.
In the dashboard at the very beginning of this article, I created 3 separate "what if" parameters to get the desired result of computing the time it takes for two trains to meet at a variable distance and speeds. You can create as many as you'd like, so go crazy with it!
If you'd like to read about Field Parameters, check out my other article here: https://www.antaresanalytics.net/post/measure-dimension-and-switch-parameters-in-power-bi-what-s-the-difference
Check out the Microsoft documentation to see some more examples. Also, if you'd like to read about how to use these parameters with Python, see my blog about Zooming into the Mandelbrot fractal with Power BI.
