Table of Contents
Introduction
In a previous blog, we discussed the limitation of searching large drop-down lists in Power BI Paginated Reports and addressed it using a text-based search field. In this article, we take that approach a step further by combining the search field with a parent drop-down selection to filter a dependent parameter. This pattern is commonly referred to as Cascading Parameters, where parameter values are selected in a top-down sequence and each dependent parameter is filtered based on the selection made above it.
What you should know
Before you start the blog, these are the things that you should be familiar with:
- Power BI and SSRS
- Paginated Reports
- Power BI Report Builder
- Basic understanding of DAX
This article builds on the approach discussed in the earlier blog, Limitations in Power BI Paginated Reports. In particular, the first limitation covered in that article provides the foundation for implementing Cascading Parameters in paginated reports.
Cascading Parameters: Premise
Consider a simple hierarchy with two columns: Continent and Country. Each column is represented by a drop-down parameter. The data is sourced from a Power BI dataset accessed through XMLA, so DAX is used to filter and shape the parameter values.
The intended flow of the report is as follows:
The user selects one or more continents from the drop-down list. Since the list is limited, a search field is not required.
The Country drop-down list is repopulated based on the selected continent values. Because this list can be large, a text-based search field is used to narrow the results.
The user then selects the required countries from the filtered list.
This approach ensures that users are guided through the selection process in a logical order while keeping large parameter lists manageable.
Working
To implement this pattern, we break the logic into three steps:
Filter the drop-down list of countries based on the keyword entered in the Search Countries text field.
Filter the list of countries based on the selected values in the Continent drop-down list.
Combine both approaches to filter the country drop-down list using both the keyword and the selected continents.
Transform your Data Reporting with Power BI!
AlphaBOLD is here to guide you through the advanced analytics landscape of Power BI. Start your journey towards more dynamic and insightful data reporting today.
Request a DemoSubproblem 1:
Assume the user wants to search for countries containing the keyword “Can.” The keyword is entered into the Search Countries text field, which is then used to filter the values displayed in the country drop-down list.
Note: This approach uses the DAX function Search(), which is case-insensitive.
To implement this, perform the following steps:
Open the Countries dataset and create a query parameter named
p_search. This parameter captures the value entered in the Search Countries text field.Bind the dataset query parameter to the report parameter so the keyword can be passed into the DAX query at runtime.
Explore more about: Optimizing DAX Queries for Power BI Performance
- Go to the Query tab of the dataset and use a DAX expression to filter the dataset based on the value entered in the text field.
Evaluate Filter(DISTINCT(‘Sheet1′[country]), SEARCH(@p_search, ‘Sheet1′[country], 1, 0))
- Save the dataset and run the report. When a keyword such as “can” is entered in the text field, the country drop-down list is filtered accordingly.
Subproblem 2:
Next, we filter the list of countries based on the continent selected in the Continent drop-down parameter. For example, selecting Asia should limit the country list to Asian countries. This implementation must also support selecting multiple continents at the same time.
To handle multi-select values, we use the DAX function PATHCONTAINS(path, item). This function evaluates whether a given value exists within a pipe-delimited string, making it well suited for parameters that allow multiple selections.
To enable this behavior:
Create a new query parameter in the Countries dataset that represents the selected continent values.
In Report Builder, convert the multi-select Continent parameter into a pipe-delimited string using an expression such as:
Pass this pipe-delimited value into the dataset query so it can be evaluated using
PATHCONTAINS().
This approach allows the country drop-down list to be filtered dynamically based on one or more selected continents.
- Navigate to the Query tab and use the following DAX expression to filter countries using the pipe-delimited parameter created earlier:
Evaluate Filter(‘Countries’, PathContains(@p_continents, ‘Countries'[Continent])) - Save the dataset and run the report. Selecting a continent such as South America will limit the Country drop-down list to countries in that region.
Until now, we have implemented both filters independently. However, the country drop-down list should respond to both the selected continents and the search keyword. To achieve this, we combine the two approaches into a single filter condition.
To do this, we use the DAX function AND(), which evaluates two expressions and returns TRUE only when both conditions are met. We can then update the dataset query accordingly.
Evaluate Filter(
‘Countries’, AND(
PathContains(@p_continents, ‘Countries'[Continent]) , SEARCH(@p_search, ‘Countries'[country], 1, 0)
))
- Save the query and run the report. When a continent such as South America is selected and a keyword like “GU” is entered, the country list is filtered to matching countries only.
You may also like: Advanced AI Analytics in Power BI for CTOs: Transforming Data Strategy
Limitations
In Power BI Paginated Reports, parameter dependencies are evaluated in a strict top-down order, meaning a parameter can only be filtered by parameters positioned above it. For example, when the Country parameter appears below Continent, the continent list cannot be filtered based on selected countries. Paginated reports also do not support circular dependencies between parameters, so a parameter can be filtered by another parameter’s value, but the reverse relationship cannot exist within the same report. In addition, parameter queries are evaluated before the report renders, which means large result sets or complex DAX logic used to implement Cascading Parameters can impact report load time. These constraints are intentional design characteristics of paginated reports and should be considered when designing parameter-driven filtering logic.
Optimize your Power BI Experience
Partner with AlphaBOLD to navigate the vast capabilities of advanced analytics. Take the first step towards smarter data insights.
Request a DemoDesigning Scalable Paginated Reports With AlphaBOLD
Designing parameter-driven paginated reports requires more than just correct DAX. It requires thoughtful report architecture, performance tuning, and a clear understanding of how Power BI datasets, XMLA, and parameter behavior interact at scale. AlphaBOLD helps organizations design and implement paginated reporting solutions that are reliable, maintainable, and aligned with real business workflows. From structuring datasets to optimizing DAX and implementing advanced patterns such as Cascading Parameters, our consultants ensure reporting solutions are built to perform consistently as data volumes and user demands grow.
Conclusion
In this article, we demonstrated how hierarchical filtering can be implemented in Power BI Paginated Reports using DAX and Power BI datasets. By combining text-based search with parent parameter selection, report designers can guide users through large datasets in a controlled and predictable way. While this approach requires careful parameter design and an understanding of report execution behavior, it remains an effective solution for complex, parameter-driven reports. When implemented correctly, Cascading Parameters help maintain usability and performance in paginated reporting scenarios that demand precision and consistency.
Happy cascading!
Explore Recent Blog Posts
