How to Take User Input in Power BI

Building a static Power BI report is useful, but building one that lets your team play with the numbers is a game changer. Instead of just presenting data, you can create a dynamic tool for scenario planning, goal setting, and forecasting. This article walks you through exactly how to take user input in Power BI to make your reports fully interactive.

Why Is User Input Important in Power BI?

Allowing users to input their own values transforms a report from a one-way street of information into an interactive conversation. Imagine your sales manager wants to see how a potential 5% increase in lead conversion rate would impact revenue. Or perhaps your finance team needs to model the effect of different cost-cutting percentages on profitability.

Instead of you having to create a dozen different versions of the same report, you can build a single dashboard that lets them answer these "what-if" questions on their own by simply moving a slider or typing in a number. This empowers your colleagues to explore data, test hypotheses, and make better-informed decisions without needing to know any DAX or touch the underlying Power Query model.

The primary tool Power BI gives us for this specific job is the What-If Parameter. Let's break down how to use it.

Using a "What-If Parameter" for Numeric Input

The What-If Parameter is your go-to feature for capturing a single, numeric value from a user that you can then use in your calculations. It's perfect for things like percentage changes, growth targets, and cost adjustments. Setting it up is a simple, three-part process: create the parameter, use it in a measure, and add it to your report canvas.

Step 1: Create the What-If Parameter

First, we need to create the parameter itself. This process will generate a new table and a simple measure for us automatically.

1. Navigate to the Modeling tab on the Power BI Desktop ribbon.
2. Click on the New Parameter button. A dialog box will appear.
3. In the dialog box, you have a few fields to configure based on the kind of input you want. Let's use an example of creating a parameter to model a "Projected Sales Growth."

Once you click "OK," you'll notice two new things on your report:

1. A new slicer has appeared on your report canvas, showing a slider and an input box for our "Sales Growth %".
2. In the Data pane, you now have a new table named "Sales Growth %".

Step 2: Understand What Power BI Just Created

This part is important. Clicking that button didn't just create a slicer, it generated a whole mechanism for capturing user input using DAX.

If you look at the new "Sales Growth %" table you just created, you'll see it contains:

• A column also named "Sales Growth %", which contains a list of numbers from your minimum (-0.5) to your maximum (1.0), incrementing by the value you set (0.05). This is the series of values that populates the slicer.
• A measure, called "Sales Growth % Value". The DAX formula for this measure is:

Selected Sales Growth % = SELECTEDVALUE('Sales Growth %'[Sales Growth %], 0.1)

This simple formula is the key. The SELECTEDVALUE() function checks the "Sales Growth %" column. If just one value is selected by the user (which is always true with a single-value slider), it returns that value. If no value is selected, it returns the default you specified (0.1 in our case).

This measure is now a dynamic variable that holds whatever value your user has selected on the slicer.

Step 3: Use the Parameter in a New DAX Measure

Now for the fun part. We can use our new Selected Sales Growth % measure inside another DAX measure to create a dynamic calculation. Let's say you already have a basic measure for total sales:

Total Sales = SUM(FactSales[Revenue])

Now, let's create a new measure to calculate the projected sales based on the user's input from the slicer. Right-click on your sales table and select "New measure".

Projected Sales = [Total Sales] * (1 + [Selected Sales Growth %])

Let's break that down:

• It takes your base [Total Sales].
• It then multiplies it by (1 + [Selected Sales Growth %]) to calculate the final projected amount. If the user selects 15% (0.15) on the slicer, this formula becomes [Total Sales] * (1 + 0.15), effectively increasing sales by 15%.

Now, add your new "Projected Sales" measure to a card or chart on your report. Put it next to your "Total Sales" visual. As you move the "Sales Growth %" slider, you'll see the "Projected Sales" value update in real-time. You've just built an interactive forecasting tool!

Taking Text Input with Slicers

What-If Parameters are brilliant for numbers, but what if you want the user to select a text-based option to change a calculation? For instance, maybe you want to apply different discount models based on a selected "Strategy" like "Aggressive," "Balanced," or "Conservative."

This isn't something that exists as a neat feature like the What-If Parameter, but we can build it easily using a disconnected table.

Step 1: Create a Disconnected Table

A disconnected table is just what it sounds like: a table you create that has no relationship to any other tables in your data model. Its only purpose is to hold the options you want to present to the user.

1. On the Home ribbon, click Enter Data.
2. A dialog box will appear, letting you create a table. In the first column, let's name it "Strategy Tier".
3. Under this column, enter the text options you want to offer: "Aggressive," "Balanced," and "Conservative."
4. Give the table a name, like "Strategy Table", and click Load.

You now have a simple one-column table in your model that contains your three strategy options.

Step 2: Create a Slicer and a Capture Measure

Next, add a new Slicer to your report and drag the "Strategy Tier" field from your new "Strategy Table" into it. You now have a slicer with your three text options.

Just like with the What-If Parameter, we need a measure to capture the user's selection:

Selected Strategy = SELECTEDVALUE('Strategy Table'[Strategy Tier], "Balanced")

This measure will now hold the text value - "Aggressive," "Balanced," or "Conservative" - that the user selects.

Step 3: Use the Text Selection in a Measure

Finally, we can use a SWITCH() function in a DAX measure to perform different actions based on the text input. Let's create a dynamic "Strategic Discount" measure:

Strategic Discount = 
SWITCH(
    [Selected Strategy],
    "Aggressive", 0.20,
    "Balanced", 0.10,
    "Conservative", 0.05,
    0.10 
)

This formula checks the value of our [Selected Strategy] measure. If it's "Aggressive", it returns 0.20 (20%). If it's "Balanced", it returns 0.10, and so on. Now, you can use this [Strategic Discount] measure in other calculations, like projecting revenue after a strategic discount is applied. Your users can freely toggle between different strategic models to see the immediate impact on their numbers.

Final Thoughts

By using What-If Parameters for numerical scenarios and disconnected tables for text-based selections, you can fundamentally change how people interact with your reports. They move from being static displays of past performance to dynamic tools for future planning, all without needing complex filters or editing access from your users.

Once you get comfortable with these techniques, you realize just how many time-consuming report variations can be replaced by a single, interactive dashboard. We built Graphed to take this a step further, allowing you to bypass much of this manual setup entirely. Instead of configuring parameters and writing DAX, you can simply ask for what you need in plain English - like "create a dashboard to compare sales versus our sales target, and add a slider to adjust the target" - and get a fully interactive result in seconds.