Remove blanks from dynamic slicer

[Silvard]

I have a parameter fields table that consists of 20 columns from various tables.

I have a multi-selection enabled slicer and I need to filter out the blank/empty values dynamically.

Imagine that we select a Category and then get all the corresponding Products, but you now see an option to select an empty/blank value. 

How can we filter these out from the slicer, keeping in mind that it has to work dynamically and work regardless how many columns included/added in the filtering.

I only want to filter out the blanks from the columns that are being used and in a measure that we can put in the filter pane and not the solution of removing blanks/empty in power query.

How can we achieve this?

I greatly appreciate your help!

[Poojara_D12]

Hi @Silvard 

To dynamically filter out blank/empty values from a multi-selection slicer without modifying the data in Power Query, you can use a DAX measure and apply it as a visual-level filter. The measure checks whether the selected columns contain blank values and excludes them. For example, using SELECTEDVALUE() or ISBLANK() in a DAX measure can help detect blanks dynamically based on slicer selections. You then apply this measure as a filter in the filter pane, setting it to show only non-blank values. This ensures that as users select different fields in the slicer, the slicer automatically hides empty values while keeping the filtering flexible across multiple columns. This approach is efficient, does not require hardcoding column names, and works regardless of how many columns are included in filtering.

[bhanu_gautam]

@Silvard 

Create a measure that checks if the selected values in the slicer are not blank. This measure will return 1 for non-blank values and 0 for blank values.

NonBlankValues =
IF(
ISBLANK(SELECTEDVALUE('YourTable'[YourColumn])),
0,
1
)

Use the Measure in the Filter Pane: Add this measure to the filter pane of your slicer visual. Set the filter condition to show only values where the measure equals 1.

Select the slicer visual.
Drag the NonBlankValues measure to the Filters pane.
Set the filter to show items when the value is 1.

 To ensure that the filtering works dynamically regardless of the number of columns included, you can create a similar measure for each column you want to filter and combine them using a logical AND operation.

DAX
NonBlankValuesDynamic =
IF(
ISBLANK(SELECTEDVALUE('YourTable'[Column1])) ||
ISBLANK(SELECTEDVALUE('YourTable'[Column2])) ||
ISBLANK(SELECTEDVALUE('YourTable'[Column3])),
0,
1
)
Adjust the measure to include all relevant columns. This measure will return 1 only if all selected columns have non-blank values.

Apply the Combined Measure: Use the combined measure in the filter pane of your slicer visual

[Silvard]

Hi @bhanu_gautam 

Thank you for your input.

Unfortunately this isn't working as I'm still seeing the blank/empty values.

Using countrows, filtering for non blank works, but the moment you get into the next column to slice for, I start to see the empty values again.

I don't want to filter out columns that aren't being filtered on so I can't just do a countrows over each column, as this becomes a static solution.