DirectQuery · Dynamic M Parameters · M · Power BI · Power Query

Dynamic What-If With User-Entered Data In Power BI

My recent post on passing any value to a Power BI dynamic M parameter from a report generated quite a lot of interest but only among people who use DirectQuery mode and who want more flexibility when filtering. In this post, however, I’ll show you an application for this technique that is useful for anyone, regardless of whether they are using DirectQuery mode or Import mode, and which is nothing to do with filtering.

You’re probably familiar with Power BI’s what-if parameter feature which allows end users to pass values into calculations. It’s great but it has one big limitation: all the values that can be used need to be hard-coded in a calculated table at design time. What if (haha) you could let your users enter the values they wanted to use in the report instead? Let’s see a simple example of how to do this.

Here’s an Import mode fact table containing sales values for various products:

The aim is to allow end users to enter one or more percentage values in a report and have these used to calculate growth forecasts on the Sales Amount value.

To do this you need to use the technique from the blog post referenced above to let the users enter the percentage values. First, create an M parameter of type Decimal Number (called EnteredValueParam) in the Power Query Editor:

Then create a dummy query (in this case called EnteredValues) with no rows and a single numeric column to bind the parameter to. Here’s the M code for this:

  type table [EnteredValues = number],

Now comes the fun part. Rather than use these entered values to filter a SQL query, they will be used to generate a SQL statement that returns the same values in the form of a disconnected, DirectQuery table connected to a relational database (SQL Server in this case). Here’s the M code for a query, in this example called ReturnedValues, that does this:

  //Check if the M parameter returns a list                                         
  //If it doesn't, turn it into a list                                    
  FilterList =
    if Type.Is(
  //Generate all the individual SELECT statements                                               
  GenerateSQLQueries = List.Transform(
    each "SELECT '"
      & Text.From(_)
      & "%' AS Forecast, "
      & Text.From(_)
      & " AS Pct"
  //Concatenate all the SELECT statements with UNION ALL into a single statement                                                                              
  UnionAll = Text.Combine(
    " UNION ALL "
  //Connect to the SQL Server database and run the query                                                      
  Source = Sql.Database(
  RunQuery = Value.NativeQuery(

If the parameter EnteredValueParam is passed the single value 1, the ReturnedValue query generates the following SQL query and runs it:

SELECT '1%' AS Forecast, 1 AS Pct

Here’s what the ReturnedValue query returns:

If the parameter EnteredValueParam returns the list {1,25,50,75}, the ReturnedValue query generates the following SQL query and runs it:

SELECT '1%' AS Forecast, 1 AS Pct 
SELECT '25%' AS Forecast, 25 AS Pct 
SELECT '50%' AS Forecast, 50 AS Pct 
SELECT '75%' AS Forecast, 75 AS Pct

Here’s what the ReturnedValue query returns in this case:

At this point, with all the queries loaded, the dataset looks like this:

The ReturnedValues table is in DirectQuery mode; the Sales table is in Import mode; the Entered Values table is in Import mode and the EnteredValues column on that table is bound to the EnteredValueParam M parameter, which has the Multi-select property turned on.

With all this in place you can create a measure on the Sales table which takes the sum of Sales Amount and applies a percentage growth based on the selected value in the Pct column of the ReturnedValues table:

Sales Amount Forecasts =
SUM ( Sales[SalesAmount] )
    * (
        ( SELECTEDVALUE ( ReturnedValues[Pct] ) / 100 ) + 1

Finally, here’s a report page that uses the Filter By List custom visual to allow end users to enter the percentage forecast values and has a matrix to display the measure above with the Product field from the Sales table on rows and the Forecast field from the ReturnedValues table on columns:

Here it is in action:

It’s a bit of a pain that you need a DirectQuery data source in order to be able to do this but it’s not too difficult to set one up and it won’t need much in the way of resources. I think this technique (which I hereby name “Dynamic DirectQuery Disconnected Dimension Tables”) could be useful in other scenarios too, such as ABC analysis. If you can think of other ways to use this let me know by leaving a comment below!

9 thoughts on “Dynamic What-If With User-Entered Data In Power BI

  1. Great post again Chris. Love your work and effort of sharing it.
    As you are using direct query (partly) anyway, you could also use an embedded canvas app to gather the table from the user, write that back in a datasource like Dataverse

  2. Thats really interesting, is it possible to execute this on a row base?

    Example Input:
    A 100
    B 50
    C 35
    D 110
    SUM 295

    In your example you multiply every value with x%, so basically the column*1.1

    I’d like to use something like this on a row base.

    So the output of the column would be:
    A 100, 110
    B 50, 57.5
    C 35, 17.5
    D 110, 88
    SUM 295, 273

    I already tried to solve it, but needed one parameter for each row, which would imply 200 Measures for my model.

    Thank you for any assistance, ideas and the article.


    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      No, because it only works in DirectQuery mode and you can’t do DirectQuery on Excel (well there are some custom connectors that allow it but last time I tried them performance wasn’t great)

  3. One workaround i have found for the limitation in PBI for what-if analysis is – Generate series upto 1M or so with interval 1 & use that field in the page filter pane instead of slicer!! It works great for integer but wont work for decimal though!!

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.