Forum Discussion

MAPM's avatar
MAPM
Frequent Visitor
4 years ago
Solved

Multiple Contains Filters using Visual Filters

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-column/m-p/767928

 

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

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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
)

     

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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
)

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • MAPM's avatar
      MAPM
      Frequent Visitor
      4 years ago

      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:

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


      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:

      IdName
      1ABCZZZZ
      2ABCZZZX
      3ABCZZZY
      8asdf
      10fffaaf

       

      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). 

  • MAPM's avatar
    MAPM
    Frequent Visitor
    4 years ago

    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: