Paginated report - optional parameter

[mkavcic]

Hi,

In paginated report, I would like to check if nothing is selected in the parameter, then this parameter would not be used in the dax query.

How can I achieve this?

For example,

I have 2 parameters that have no relation to each other.

One is for color of a car, the other for a brand of the car.

If I select the color but not the brand, I want the dax query to filter only on color.

I don't want the dax query to filter on the color and all brands.

When I will be using URL's for report execution, I don't want to pass the parameter for brand, if it was not used in the parameter filter.

Thank you!

[Surendar88]

Hi @mkavcic 

If there is no default value for the optional parameter, then how would we handle it. 

I need help with DAX query, how to ignore the optional parameters. If I use or condition to check if its blank or the value passed, it is very slow. 

It will be very helpful for me. 

[mkavcic]

Hi @Surendar88 

You should add a union sql to the dataset for the parameter. In the union add 'All' to the dataset.

Then in parameters, add 'All' option as the default option.

Then in main report dataset query, you need to edit query in VBA code where you check for 'All' option in the parameter and if it's there, then you don't use this parameter in the query, and if it's selected, then you use it in the query. 
So you created a dynamic query.

Hope it helps.

[mkavcic · Accepted solution]

Hi,

Thank you for your answer!

I did solve it in this way:

1. Add a 'dummy' item called 'All' to the list of items in parameter query.

2. Make the new item 'All' defualt. You could also use 'Null' here.

3. Modified the DAX query in VBA with query.commandtext where I entered the command IIF(Parameter.Value = "All", "", "__Usefilter"), meaning if there is 'All' selected then do not use the filter, but if one item is selected then use the filter.

Hope it helps.

Thank you!

[v-rongtiep-msft]

Hi @mkavcic ,

The easiest way to make a parameter optional is to set a default value for the parameter.

Power BI – Report Builder using Multi Value Parameters in DAX 

• Create a Data Set with the SLICER value in PBI Desktop
• Copy the PBI Query. Run the Performance Analyzer in PBI Desktop
• Create new Parameter referencing to the Main Data Set Query Filter
• Add a Parameter in the Main Sales Data Set

I have also found a similiar post, please refer to it to see if it helps you.

DAX for PowerBI Paginated Report - Multiple Values Selected 

• you have to use RSCUSTOMDAXFILTER function for multiple values in your Power BI Report builder DAX expression. 

Best Regards

Community Support Team _ Polly

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