Paginated report - filter multiple columns based on a single parameter

[jkossy]

Hi community

I'm making my first foray into using Paginated Reports in PBI but am getting a bit stuck with the following.

I have a dataset that is an extract of my Power BI semantic model that loads a table when either say Column A, B or C has a "Y" value. I want to add a parameter with text, code values more familiar to end users that correspond to those columns, say "001", "002" and "003". When 001 is selected the table should filter Column A to Y values etc. If nothing is selected the original table definition should load. 

I'm sure this must be possible but all my attempts have failed thus far 😞 any pointers in the right direction greatly appreciated.

jkossy

[DallasBaba]

@jkossy Can you use three separate parameters instead of one, each named after the respective code values ("001", "002", and "003")?

1 - In the Filter pane of each parameter, use Basic filtering to offer users a dropdown list with only "Y" as an available value. This preserves the user-friendly code representation.
2 - In the Dataset Properties for each parameter, link it to the corresponding column ("Column A", "Column B", and "Column C") for filtering.

But if you prefer using a Single Parameter with Expression, I suggest using an expression to handle the logic across multiple columns. This approach requires building an expression in the Parameter Value field.
The expression can leverage the SWITCH function to evaluate the selected parameter value and apply the appropriate filter condition.  Here's an example:

SWITCH(
  Parameters!MySingleParameter.Value,
  "001", Fields!ColumnA.Value = "Y",
  "002", Fields!ColumnB.Value = "Y",
  "003", Fields!ColumnC.Value = "Y",
  TRUE
)

 The above expression checks the parameter value:
If "001", it filters Column A for "Y" values.
If "002", it filters Column B for "Y" values.
If "003", it filters Column C for "Y" values.
If nothing is selected (blank or null), it applies no filter (TRUE).

You can also refer to the following resource for additional information on creating parameters for paginated reports

https://www.youtube.com/watch?v=YU1VjMEOw9Y

https://learn.microsoft.com/en-us/power-bi/paginated-reports/parameters/paginated-reports-create-par...

 If my answers help you arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Best Regards,

Dallas.

[jkossy]

Thanks for your response Dallas.

I have that working for one parameter but the parameter I'm trying to create I assume needs some sort of expression? Something that would say if value 001 is selected by the user filter the table or dataset where Column A has Y values, if 002 selected filter Column B where Column B has a Y value (regardless of values in Column A), if nothing is selected don't filter.  I can't work out how to bring these conditons together as the one parameter needs to apply to three columns?

Thanks

[DallasBaba · Accepted solution]

@jkossy 

Yes, you can use a parameter to filter a table in a paginated report in Power BI Report Builder:

Steps:

1 - In the Report Data pane, right-click the Parameters node and click Add Parameter.
2 - In the Report Parameter Properties dialog box, type the parameter's name or accept the default name in the Name field.
3 - In the Prompt field, type the text that appears next to the parameter text box when the user runs the report.
4 - Select the data type for the parameter value in the Data type field.
5 - If the parameter can contain a blank value, select Allow blank value. If the parameter can contain a null value, select Allow null value.
6 - To allow a user to select more than one value for the parameter, select Allow multiple values.
7 - Set the visibility option. Select Visible to show the parameter on the toolbar at the report's top. Select Hidden to hide the parameter so it doesn't display on the toolbar. To hide the parameter and protect it from being modified on the report server after the report is published, select Internal
8 - Confirm: Click "OK."

Using the Parameter to Filter a Table:

1 - Access dataset properties: Right-click the dataset in the Report Data pane and select "Dataset Properties."
2 - Navigate to the Parameters tab: Click on it within the dialog box.
3 - Link the parameter to a column:
- Parameter Name: Enter the name of the created parameter.
- Value: Enter the name of the column to filter.
4 - Save changes: Click "OK."

When a user runs the report and selects a value for the parameter, the table will dynamically filter based on their choice, providing customized data views.

You can watch Patrick's video on YouTube.

https://www.youtube.com/watch?v=TEIdI5bFCSU

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly.  

Best Regards,

Dallas.