Removing Nulls from Parameter table in Paginated Report Builder (Dax)

[jhalverson]

Hi all,

I am trying to get my paginated report to have "Select All" as the default for all of my parameters, but I cannot get it to work and I beleive it is due to the (null) values appearing in the parameter tables:

The market column in the main data source this is being pulled from does not have any nulls, so I think the dax code is adding it in with that h0 or something. There is a lot going on in that auto generated dax query, and I couldn't figure out what to add or remove to get rid of that null value.

So my question is what exactly is the best way to get rid of that null row? Also is the null there for a reason, and would removing it potentially cause issues elsewhere? 

Thanks!

Here is the dax if it is helpful. Market is a column (being used as a parameter) in a table called Board Members Detail:

EVALUATE SELECTCOLUMNS (ADDCOLUMNS (SUMMARIZECOLUMNS (ROLLUPADDISSUBTOTAL ('Board Members Detail'[Market], "h0")), "ParameterLevel", if ([h0], 0, 1)), "ParameterCaption", SWITCH([ParameterLevel], 1, 'Board Members Detail'[Market], "Blank()"), "ParameterValue", 'Board Members Detail'[Market], "ParameterLevel", [ParameterLevel], "'Board Members Detail'[Market]", 'Board Members Detail'[Market]) order by 'Board Members Detail'[Market], [ParameterLevel]

[v-rongtiep-msft]

Hi @jhalverson ,

You can try  @AmiraBedh ‘s code. Also refer to my steps.

1. Open your paginated report in Power BI Report Builder.

2. In the Report Data pane, right-click the Parameters node > Add Parameter. The Report Parameter Properties dialog box opens.

3. In Name, type a name for the parameter or accept the default name.

4. In Prompt, type text to appear next to the parameter text box when the user runs the report.

5. In Data type, select the data type for the parameter value.

6. If the parameter can contain a blank value, select Allow blank value.

7. If the parameter can contain a null value, select Allow null value.

More details: Create parameters for paginated reports in the Power BI service - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

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

[jhalverson]

This is a multi-value parameter, so when I click accept null values it fails:

[AmiraBedh]

Just add FILTER to exclude NULL values :

EVALUATE SELECTCOLUMNS (ADDCOLUMNS (SUMMARIZECOLUMNS (ROLLUPADDISSUBTOTAL ('Board Members Detail'[Market], "h0")), "ParameterLevel", if ([h0], 0, 1)), "ParameterCaption", SWITCH([ParameterLevel], 1, 'Board Members Detail'[Market], "Blank()"), "ParameterValue", 'Board Members Detail'[Market], "ParameterLevel", [ParameterLevel], "'Board Members Detail'[Market]", 'Board Members Detail'[Market]) order by 'Board Members Detail'[Market], [ParameterLevel]

[jhalverson]

Hi @AmiraBedh  thank you for the reply!

I guess my issue is I am not sure where to put the actual filter syntax, what you put there was the exact same dax code I originally posted.

I tried this:

EVALUATE SELECTCOLUMNS (ADDCOLUMNS (SUMMARIZECOLUMNS (ROLLUPADDISSUBTOTAL (FILTER('Board Members Detail'[Market], 'Board Members Detail'[Market] <> BLANK()), "h0")), "ParameterLevel", if ([h0], 0, 1)), "ParameterCaption", SWITCH([ParameterLevel], 1, 'Board Members Detail'[Market], "Blank()"), "ParameterValue", 'Board Members Detail'[Market], "ParameterLevel", [ParameterLevel], "'Board Members Detail'[Market]", 'Board Members Detail'[Market]) order by 'Board Members Detail'[Market], [ParameterLevel]

But it resulted in this error: 

I also tried putting the filter function in a few other spots before that but got a max arguments is 2 error... Any idea on where to put the filter function? Thanks!