Multiple Contains Filters using Visual Filters

[MAPM]

I'm currently building a report based on a Cube built by a different team. This means I cannot create custom columns or add additional datasets. I am limited to using Measures, Bookmarks, and Visual-Page-Report level filters.

I have the need to filter on multiple "contains" strings for 1 column. Similar to this question:

https://community.powerbi.com/t5/Desktop/How-can-I-put-multiple-quot-Contain-quot-filter-on-one-colu...

Is there any way to do this without creating custom calculated columns?

[tamerj1 · Accepted solution]

Hi @MAPM 
Please refer to sample file with the proposed solution https://www.dropbox.com/t/Nd2LhJFlC0H1ZlwJ

This requires creating a filter table with the desired strings. Can be created with code if you give more details. For now I created with hard code.

Then create the filter measure, place it in the filter pane, select "Is not blank" and apply the filter.

FilterMeasure = 
IF (
    COUNTROWS ( ALL ( FilterTable[Selection] ) ) <> COUNTROWS ( ALLSELECTED ( FilterTable[Selection] ) ),
    COUNTROWS (
        FILTER (
            VALUES ( FilterTable[Selection] ),
            CONTAINSSTRING ( 
                SELECTEDVALUE ( DimID[Name] ),
                FilterTable[Selection]
            )
        )
    ),
    1
)

[MAPM]

To add some additional context. The closest I've gotten to what I need is by creating a measure like this:

ContainsRelevantStrings = 

CALCULATE (
    IF(COUNTROWS ( t1 )>0,1,0),
    FILTER (
        t1 ,
        CONTAINSSTRING ( t1[Name], "ABC" ) = TRUE ()
            || CONTAINSSTRING ( t1[Name], "fffa" ) = TRUE ()
            || CONTAINSSTRING ( t1[Name], "asdf" ) = TRUE ()
    )
)

Putting that measure as a Visual Filter on a slicer with the DimensionTable[Name] and setting that Measure Visual Filter to = 1. 

This gives me a slicer with all the relevant Dimension Names. The problem is that if I click "Select All" on that Slicer it still selects ALL the Dimension[Name] rows.

This seems to be an issue other people are running into too:

• https://community.powerbi.com/t5/Desktop/Select-All-doesn-t-work-when-filtering-a-slicer/m-p/721351
• https://community.powerbi.com/t5/Issues/quot-Select-all-quot-behavior-on-a-slicer-while-visual-level...

[tamerj1]

Hi @MAPM 
Yes possible. Would you please provide some sample data along with the expected result?

[MAPM]

It's very similar to the question I linked to, but to provide an example.

I have a dimension table with 1000s of rows, for example:

Id	Name
1	ABCZZZZ
2	ABCZZZX
3	ABCZZZY
4	AAAZZZZ
5	AAAZZZY
6	AAAZZZX
7	AAAZZZC
8	asdf
9	assdf
10	fffaaf
11	fffffff

This Dimension table links to several metric tables.

I am trying to filter the full report based on filtering this dimension "Name" column, using a contains of several strings (give or take 12 strings). 

To simplify & using the example above. Imagine I'd like to filter the Dimension table for any string that contains:
"ABC", "fffa", "asdf"

I would expect the corresponding Dimension table to then filter to:

Id	Name
1	ABCZZZZ
2	ABCZZZX
3	ABCZZZY
8	asdf
10	fffaaf

Which would in turn filter my metric tables.

Normally I'd create a Calculated column that would help me achieve this, but that's not an option for my current setup (because I'm reporting using a Cube that is owned and maintained by another team).