Parameters In Power BI Desktop

Introduction

Query Parameters allow users to easily make parts of reports and data models based on one or more parameter values. By attaching Query Parameters to a report, the user may render the query elements based on those parameters that were generated.

Creating Parameters

Open Power Query Editor by clicking on ‘Transform data’ at the ‘Home’ tab of ‘Power BI Desktop’.

In the ‘Power Query Editor’ window, from the ‘Home’ tab select ‘Manage Parameters’ to bring up the ‘Parameters’ dialog, as shown below

The new ‘Manage Parameters’ dialog will open and it contains the following elements that allow you to build new parameters

• Name
  Includes the parameter by name.
• Description
  Once parameter information is displayed, the definition is displayed next to the parameter name.
• Required
  The checkbox determines whether users may define a value that needs to be given for the parameter.
• Type
  Applies a limitation on data form to the input value for the parameter
• Suggested values
  You can further limit entries that users can choose for a given parameter or provide for that. When specifying the parameter value, users can select one of the available values (Any values, List of values, Query).
• Default value
  Sets a parameter with the default value. Present value: Defines the value of the parameter for the current value.

Let see how to create a parameter. I described all the above terms, so now I mention all these fields into the ‘Manage Parameter’ window.

Here, I am creating the parameter for the database that will be used to add database tables in Power BI. We have multiple databases in our SQL server, so we will connect our database through parameters.

You can check my article on connecting SQL server in Power BI.

Now I am providing the values of the above segments.

• Name
  In this segment, I will provide the name of the parameter i.e. ‘Database’
• Description
  I will use this parameter to connect the database so that my description is relevant to my task.
• Required
  Click the checkbox of this segment.
• Type
  I will take the ‘Text’ type parameter
• Suggested Values
  I will select the ‘List of values’ and give the name of my database.
• Default value
  I will choose the first database value as my default value.
• Current Value
  Same as the Default value.

Click the OK button and we can see that the parameter Database has been generated, which is shown in the Left Pane and also in the Center Pane.

Summary

I hope you understood how you can create a parameter. In the next article, I will show you how to use this parameter to import the database table, so stay with me and wait for the next article. Thanks for reading.