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:

#table(
  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:

let
  //Check if the M parameter returns a list                                         
  //If it doesn't, turn it into a list                                    
  FilterList =
    if Type.Is(
      Value.Type(EnteredValueParam),
      List.Type
    )
    then
      EnteredValueParam
    else
      {EnteredValueParam},
  //Generate all the individual SELECT statements                                               
  GenerateSQLQueries = List.Transform(
    FilterList,
    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(
    GenerateSQLQueries,
    " UNION ALL "
  ),
  //Connect to the SQL Server database and run the query                                                      
  Source = Sql.Database(
    "localhost",
    "AdventureWorksDW2017"
  ),
  RunQuery = Value.NativeQuery(
    Source,
    UnionAll
  )
in
  RunQuery

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 
UNION ALL 
SELECT '25%' AS Forecast, 25 AS Pct 
UNION ALL 
SELECT '50%' AS Forecast, 50 AS Pct 
UNION ALL 
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!