Pass slicer values from Desktop to paginated report optional parameter

[SowjanyaB]

I have a paginated report with different parameters, one being optional parameter. I have added this paginated report into Desktop report and mapped the parameters with PowerBI field.

It is working as expected when i select any value from slicer, it is getting passed to parameter in paginated report.

When deselect all from one of the slicer which is mapped to optional parameter in paginated report, it is selecting all the values and passing to paramter and applying to where clause.

Is there anyway to build optional slicers in Power BI?

[SowjanyaB]

Thank you for the quick response. Our datasource is IBM DB2 and we are bound to use only Direct Query method due to business reasons. Does the suggested solution above still works?

[VahidDM]

Hi @SowjanyaB 

Yes, you can simulate optional slicers in Power BI by adding a specific option to represent "no selection." Here's how:

Create a Parameter Table with an Additional "(None)" Option:

ParameterTable = 
ADDCOLUMNS(
  DISTINCT('YourTable'[ParameterField]),
  "ParameterValue", 'YourTable'[ParameterField]
)
UNION(
  ROW("ParameterField", "(None)", "ParameterValue", BLANK()),
  VALUES('YourTable'[ParameterField])
)

Use the New Table in Your Slicer:

• Replace your original slicer field with ParameterTable[ParameterField].
  This slicer now includes an option "(None)" that represents no selection.

Modify Your Measures to Handle the "(None)" Selection:

SelectedParameter =
IF(
  SELECTEDVALUE(ParameterTable[ParameterField]) = "(None)",
  BLANK(),  // Treat as optional
  SELECTEDVALUE(ParameterTable[ParameterField])
)

Pass the Selected Parameter to the Paginated Report:

• Map SelectedParameter to the corresponding parameter in your paginated report.
• Ensure your paginated report treats a blank or null parameter as optional.

By adding a "(None)" option, users can choose it to indicate that the parameter should not filter data, effectively making it optional.

Note: Replace 'YourTable' and [ParameterField] with your actual table and field names.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube