Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
APPLIES TO:
Power BI Report Builder
Power BI Desktop
Cascading parameters provide a way of managing large amounts of data in a Power BI paginated report. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter is independent and might present a list of product categories. When the user selects a category, the second parameter is dependent on the value of the first parameter. Its values are updated with a list of subcategories within the chosen category. When the user views the report, the values for both the category and subcategory parameters are used to filter report data.
To create cascading parameters, you define the dataset query first and include a query parameter for each cascading parameter that you need. You must also create a separate dataset for each cascading parameter to provide available values. For more information, see Add, Change, or Delete Available Values for a Report Parameter (Power BI Report Builder).
Order is important for cascading parameters because the dataset query for a parameter later in the list includes a reference to each parameter that is earlier in the list. At run time, the order of the parameters in the Report Data pane determines the order in which the parameter queries appear in the report, and therefore, the order in which a user chooses each successive parameter value.
In the Report Data pane, right-click a data source, and then click Add Dataset.
In Name, type the name of the dataset.
In Data source, choose the name of the data source or click New to create one.
In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
In Query, type the query to use to retrieve data for this report. The query must include the following parts:
A list of data source fields. For example, in a Transact-SQL statement, the SELECT statement specifies a list of database column names from a given table or view.
One query parameter for each cascading parameter. A query parameter limits the data retrieved from the data source by specifying certain values to include or exclude from the query. Typically, query parameters occur in a restriction clause in the query. For example, in a Transact-SQL SELECT statement, query parameters occur in the WHERE clause.
Click Run (!). After you include query parameters and then run the query, report parameters that correspond to the query parameters are automatically created.
Note
The order of query parameters the first time you run a query determines the order that they are created in the report. To change the order, see Change the Order of a Report Parameter (Power BI Report Builder)
Select OK.
Next, you will create a dataset that provides the values for the independent parameter.
In the Report Data pane, right-click a data source, and then click Add Dataset.
In Name, type the name of the dataset.
In Data source, verify the name is the name of the data source you chose in step 1.
In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
In Query, type the query to use to retrieve values for this parameter. Queries for independent parameters typically do not contain query parameters. For example, to create a query for a parameter that provides all category values, you might use a Transact-SQL statement similar to the following:
SELECT DISTINCT <column name> FROM <table>
The SELECT DISTINCT command removes duplicate values from the result set so that you get each unique value from the specified column in the specified table.
Click Run (!). The result set shows the values that are available for this first parameter.
Select OK.
Next, you will set the properties of the first parameter to use this dataset to populate its available values at run-time.
In the Report Data pane, in the Parameters folder, right-click the first parameter, and then click Parameter Properties.
In Name, verify that the name of the parameter is correct.
Click Available Values.
Click Get values from a query. Three fields appear.
In Dataset, from the drop-down list, click the name of the dataset you created in the previous procedure.
In Value field, click the name of the field that provides the parameter value.
In Label field, click the name of the field that provides the parameter label.
Select OK.
Next, you will create a dataset that provides the values for a dependent parameter.
In the Report Data pane, right-click a data source, and then click Add Dataset.
In Name, type the name of the dataset.
In Data source, verify the name is the name of the data source you chose in step 1.
In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
In Query, type the query to use to retrieve values for this parameter. Queries for dependent parameters typically include query parameters for each parameter that this parameter is dependent on. For example, to create a query for a parameter that provides all subcategory (dependent parameter) values for a category (independent parameter), you might use a Transact-SQL statement similar to the following:
SELECT DISTINCT Subcategory FROM <table>
WHERE (Category = @Category)
In the WHERE clause, Category is the name of a field from <table> and @Category is a query parameter. This statement produces a list of subcategories for the category specified in @Category. At run time, this value will be filled in with the value that the user chooses for the report parameter that has the same name.
Select OK.
Next, you will set the properties of the second parameter to use this dataset to populate its available values at run time.
In the Report Data pane, in the Parameters folder, right-click the first parameter, and then click Parameter Properties.
In Name, verify that the name of the parameter is correct.
Click Available Values.
Click Get values from a query.
In Dataset, from the drop-down list, click the name of the dataset you created in the previous procedure.
In Value field, click the name of the field that provides the parameter value.
In Label field, click the name of the field that provides the parameter label.
Select OK.
Click Run.
From the drop-down list for the first, independent parameter, choose a value.
The report processor runs the dataset query for the next parameter and passes it the value you chose for the first parameter. The drop-down list for the second parameter is populated with the available values based on the first parameter value.
From the drop-down list for the second, dependent parameter, choose a value.
The report does not run automatically after you choose the last parameter so that you can change your choice.
Click View Report. The report updates the display based on the parameters you have chosen.
Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreTraining
Module
Configure Power BI report filters - Training
Report filtering is a complex topic because many techniques are available for filtering a Microsoft Power BI report. However, with complexity comes control, allowing you to design reports that meet requirements and expectations.
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.