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

     

tamerj1's avatar
tamerj1
Community 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).