Paginated Report Multivalue Parameter and DAX to include (Blank)

[prathyoo]

We are developing a paginated report that connects to a Power BI dataset and we wish to incorporate a multivalue filter on a column that includes null values. In the Power BI interactive reports we can filter the reports to include or exclude the null values -

This is automatic as Power BI realizes the existence of null and provides an option called (Blank). Now, we tried to display the above drop down in Paginated Reports and initially, it would not show the (Blank) or Null option. So, we had to write a DAX to include a an option "Blank" as a string literal -

But, now we are stuck at how to translate the above multivalue pick by end user into a DAX filter. We need to somehow trnaslate the "Blank" option to a Blank() filter when the user has checked this option.

Currently we use SUMMARIZECOLUMNS with the below filter -

FILTER
(
VALUES('DIM:Claim Header'[Incident Type]),

PATHCONTAINS(
@IncidentType,'DIM:Claim Header'[Incident Type])
)

But, we are struggling to figure out how we can change the filter to include retrieving data with no value in Incident Type when the user also ticks "Blank" in the dropdown.

[prathyoo · Accepted solution]

We have fixed the this with the following filter clause -

  FILTER (
                VALUES ( 'DIM:Claim Header'[Incident Type] ),
                PATHCONTAINS ( @IncidentType, 'DIM:Claim Header'[Incident Type] )||IF(FIND("Blank",@IncidentType),ISBLANK('DIM:Claim Header'[Incident Type]))
            )

We added an OR clause to include the ISBLANK filter in case the value "Blank" is included in the Paginated Report parameter.