Input string parameter in Power BI

[rizwanmalik]

Hi all,

I have a requirement to migrate a Tableau report to Power BI. In tableau, there is a text parameter that takes a string from end user and pass it on to a stored procedure that runs and brings the results in the report. I want to create something similar in Power BI. Can anyone please advise me how can I create a parameter that takes an input string from end user and pass it to a stored procedure? As pe my research, there is a What if parameter that can be shown in the PBI report but the problem is that it only takes numeric values. I want to use a parameter that takes a string. 

Any solutions or workarounds are welcomed. 

[Anonymous]

Hi @rizwanmalik, Do you tried changing the type of Parameter, Make sure it is text type then it will accept strings. I have shared few steps below as well via which you can create the parameter.

• Create a Text Parameter:

  • Go to the 'Modeling' tab in Power BI Desktop.
  • Click 'New Parameter', set it to 'Text', and define its properties.

• Create a Parameter Table:

  • Go to 'Modeling' and select 'New Table'.
  • Use DAX to create a table with a single row, which will hold the parameter value.

• Use the Parameter in Power Query:

  • Open 'Transform Data' to access Power Query Editor.
  • Create a new query to call your stored procedure, incorporating the parameter value in the SQL statement.

• Bind Parameter to Report Elements:

  • In the 'Report' view, add a slicer or text box linked to the parameter.
  • Configure visualizations to use the parameterized query.

• Publish and Configure:

  • Save and publish your report to Power BI Service.
  • Ensure dataset settings allow user interaction with the parameter.

[gulraiz]

you can achieve this in Power BI Desktop by creating Parameters in the Power Query Editor. You can open the Power Query editor by clicking on 'Transform Data' in PBI Desktop.

Here is a link to a youtube video explaining the method: https://youtu.be/9rRFh6UQfmY?si=uwp_O1Q4ErIlW2K1

[Aqibzulfiqar1]

1. Power Apps Integration

You can integrate Power Apps with Power BI to create a text input field. The input from Power Apps can then be used to call a stored procedure. Here’s a high-level approach:

• Create a Power App with a text input box.
• Embed the Power App in your Power BI report.
• Use the text input value from Power Apps to call the stored procedure in Power BI.

2. Using Query Parameters in Power BI

Although Power BI doesn’t natively support text input parameters in the report view, you can create a query parameter in Power Query:

• Go to Power Query Editor.
• Create a new parameter with a text data type.
• Use this parameter in your query that calls the stored procedure.

3. Custom Visuals or Slicers

While default slicers don’t support text input, there are custom visuals available in the Power BI marketplace that allow for text input. One such visual is the “Text Filter”:

• Import the “Text Filter” visual from the marketplace.
• Add it to your report and link it to your data.
• Use DAX or Power Query to utilize the text input in your stored procedure.

4. Direct Query and SQL Parameters

If your data source supports Direct Query mode, you can pass the parameter directly to the SQL query:

• Write a custom SQL query in Power BI.
• Use a parameter in your SQL query that takes the input from a text box created using custom visuals.

[miTutorials]

Check out the Tutorial here if you need some more guidance !!

Setup Dynamic Parameters to Filter data in your PowerBI Report | MiTutorials (youtube.com)

[rajendraongole1]

Hi @rizwanmalik -In Power BI, you can create a parameter that takes a string input from the end user and pass it to a stored procedure using a combination of Power Query and dynamic M code

Go to Home > Transform data to open the Power Query Editor.
In the Power Query Editor, go to Manage Parameters > New Parameter.
Name your parameter (e.g., UserInput), set the data type to Text, and set the default value if desired.
Click OK.

Ref:

Parameters - Power Query | Microsoft Learn

Power Query and Stored Procedures with Parameters | Reeves Smith's SQL & BI Blog (wordpress.com)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!