Forum Discussion
How do I filter the second parameter based on the first parameter in Power BI Report Builder?
I am working with some data which contains a list of football players of various nationalities. Here is a sample of that data:
| ID | Player | Nationality |
| 1 | Roberto Baggio | Italy |
| 2 | Hernan Crespo | Argentina |
| 3 | Lionel Messi | Argentina |
I created two parameters. The first one should filter the Nationality and the second one should filter the player. Here is an image of what I see:
When I select Nationality the dropdown for 'Player' still shows me all of the list of players irrespective of their nationality. How do I get the 'Player' drop down to filter based on what I select for 'Nationality'?. E.g. so that if I select England, I would only see Rooney and Joe Cole for the 'Player' drop down?. Here is my rdl file:
https://www.mediafire.com/file/ljbfis2awvadt7b/Football.rdl/file
I'm mindful that you wouldn't be able to run the report but you would atleast be able to view settings for the datasources and parameters.
What you are looking is cascading parameters effect.
To read and understand: https://docs.microsoft.com/en-us/power-bi/guidance/paginated-report-cascading-parameter
I saw your .rdl file, pls change these only to Dataset2
Query:
SELECT * FROM Sheet1$ WHERE [Sheet1$].Nationality = @Nationality"Dataset 2" Parameters:
and try running the report.
hope this helps!
7 Replies
What you are looking is cascading parameters effect.
To read and understand: https://docs.microsoft.com/en-us/power-bi/guidance/paginated-report-cascading-parameter
I saw your .rdl file, pls change these only to Dataset2
Query:
SELECT * FROM Sheet1$ WHERE [Sheet1$].Nationality = @Nationality"Dataset 2" Parameters:
and try running the report.
hope this helps!
Thanks for your help on the paramters before. I actually tried working on three parameters today after reading the guidance you shared. I was find with one parameter, then two worked fine. As soon as I got to the third parameter I hit a brick wall. I shared the problem here:
https://community.powerbi.com/t5/Desktop/Cascading-Report-in-Power-BI-Report-Builder-Causing-Errors/m-p/2478411#M883027
feel free to take a look if you like.You cannot have NAME as parameter. It is a reserved word.
Try changing to "Param_Name" or "NAME1" and keep the prompt as "Name"
apply to the dataset filter the same change and see if it works!
Hi sevenhills
This worked beautifully. Thank you so much for your help. I'll have a read through the documentation you shared, I wasn't aware of the technical term for it until you mentioned it.
I am curious to know, if I added another parameter, club. So say I would filter by nationality ->club -> player. Would I simply add another condition to the SQL clause above and then add a parameter? like so:SELECT * FROM Sheet1$ WHERE [Sheet1$].Club = @Club AND [Sheet1$].Nationality = @NationalityYes, that is how it works.
For a report with cascading parameters,
a) Get the list of parameters - both cascading and non-cascading
b) For each non-cascading parameters without data from the source, like date range, you may not need any source i.e., no need for dataset.
c) For each non-cascading parameters with data from the source, you get the data from the source by adding dataset and using parameter value filters if apply to your need.
d) For each cascading parameter, top level is same as above.
e) For each cascading parameter,
second level and each level down,
if you need data from the source,
add a dataset with the parameters upto previous level (which you want to filter), adjust the sql or code to filterand point the parameter to this dataset.
f) Repeat the same for each level of cascading parameter. when you do for the first time, it may take some time back and forth. The concept is same as more levels of cascading added
g) Finally, to the actual dataset(s) that you consume for reporting,
you need to add logic of filter from each of the parameters
and apply parameter names to parameter values.
SSRS and Paginated reports are the same, FYI.
There are sometimes, few exceptions like you may NOT need all parameters for the reporting dataset and ONLY need the last parameter or some of the parameters for the reporting dataset, purely on your needs.
Thanks for the detailed response. This may take me some time to fully digest. I'll run some demo reports and play around with non-cascading and cascading parameters. At some point I'm sure it would all just click. I was wondering though why do the parameters need to be linked to a seperate dataset instead of being linked to the main dataset?.
I honestly have never used SSRS. I've only very recently finished a course on SQL and began using SSMS and Report builder properly. I'm enjoying the process though. Again, I appreciate the help.
