Introduction
In my previous blog, I mentioned the limitation of searching drop-down lists. We overcame that limitation by using a search field. In this blog, we are going one step further. We will use the search field and the result of the drop-down list to filter the third parameter. In classic SSRS terminology, we use the term ‘Cascading Parameters’ to define this problem. Like a waterfall, the parameter values are selected from top to bottom. The parameters positioned below are called dependent parameters because their value depends upon the selection of parameters above them.
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
Also, we will build upon the work done in the previous blog, Limitations in Power BI Paginated Reports, so you should check it out first. More specifically, the first limitation mentioned in that blog is the one you need to understand before moving on with this blog on Cascading Parameters.
Cascading Parameters: Premise
Let’s say we have a hierarchy of two columns: Continent and Country. For each of these columns, we have a drop-down list. The data is sourced through a Power BI dataset through XMLA. Hence, we will use DAX to slice and dice the data. The desired flow for this report is as such:
- The user selects a continent from the drop-down list. Since there are only seven continents, we don’t need a search field to search through the list.
- The drop-down list of countries should be repopulated based on the continent’s selection. Since there can be many countries, we also need a search field in which we will provide keywords to narrow down the list further.
- The user then selects the desired countries.
Working
We will use divide and conquer to split the task into three subproblems:
- Filter the drop-down list of cities based on the keyword provided in the ‘Search Countries’ text field.
- Filter the list of countries based on the selection of the Continents drop-down list.
- Combine the above two approaches and filter the drop-down list of countries based on both the keyword and the 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:
Let’s say we want to search all countries with the keyword ‘Can’. We will write the keyword ‘Can’ in the ‘Search Countries’ text field, and it will filter the drop-down list accordingly.
Note: We will use the DAX function Search(), which is case-insensitive.
The result should look like this:
To achieve this result, we perform the following actions:
- Open the dataset of ‘Countries’ and create a parameter ‘p_search’, which will hold the value of the text field parameter.
- Go to the ‘Query’ tab of the dataset and use the DAX expression to filter the dataset based on the text field.
Evaluate Filter(DISTINCT(‘Sheet1′[country]), SEARCH(@p_search, ‘Sheet1′[country], 1, 0))
- Save the dataset and run the report. When we write ‘can’ in the text field and press the tab, it will filter the countries.
Subproblem 2:
We also want to filter the countries based on the continent selected from the drop-down field. Suppose we select a continent ‘Asia.’ It should filter the ‘Countries’ drop-down to select all the Asian countries. Furthermore, we also need to be able to select multiple continents at once.
For this purpose, we will use the DAX function PATHCONTAINS(path, item). This function takes a pipe-delimited text of values (for example, Asia|Europe|Africa) known as a path and an item to search for in this path (for example, Africa). It returns true if that item is found in the path.
- To achieve this result, we create a new parameter in the Countries dataset that takes the values of the Continent drop-down field and creates a pipe-delimited string from the array of selections. This step ensures that we can filter countries by selecting multiple continents instead of one continent at a time.
- Navigate to the Query tab and use the following DAX expression to filter countries based on the pipe-delimited parameter created above.
Evaluate Filter(‘Countries’, PathContains(@p_continents, ‘Countries'[Continent])) - Save the dataset and run the report. When we select South America from the drop-down list, it shows only South American Countries.
Until now, we have solved both problems separately, but we should be able to search countries by continent and text. So now it’s time to combine the above two sub-solutions and solve the bigger problem.
- We will use the AND() operator in DAX, which gets two expressions as input and returns true if both are true. Otherwise, it returns false.
- Update the query using the following DAX expression.
Evaluate Filter(
‘Countries’, AND(
PathContains(@p_continents, ‘Countries'[Continent]) , SEARCH(@p_search, ‘Countries'[country], 1, 0)
))
- Save the query and run the report. We get the desired result when we choose South America and search for countries with the keyword ‘GU’ in their name.
Limitations
In Paginated Reports, we can’t filter a parameter’s value based on another parameter positioned below it. For example, in the picture below, the Country parameter is below the Continent, so we can’t filter Continent based on Country.
Paginated Reports don’t support circular dependency between parameters. This means that we can either filter Parameter A based on Parameter B, or we can filter Parameter B based on the value of Parameter A, but we can’t support both in a single report.
Further Read: Advanced AI Analytics in Power BI for CTOs: Transforming Data Strategy
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 DemoConclusion
In this blog, we took an example of geographical data to explain how we can cater to cascading parameters in Paginated Reports that source data from Power BI datasets. We used DAX to slice and dice the data along the hierarchy. We filtered a drop-down list of countries in three different ways: Based on a text in a text field, a result of a drop-down list of continents, and combining both the text field and the continent drop-down list. In the end, we mentioned some limitations that go around with the cascading parameters in SSRS. Hopefully, this article will help you solve any problems related to the cascading parameters.
Happy cascading!
Explore Recent Blog Posts
