Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure for CONTAINS Filter

I have a data field that is a multislect in the application that stores data with semicolon devider. 

 

ProductSupplier
123AAA
234AAA;BBB
345AAA;CCC
567AAA;BBB;CCC

 

 

I need to build a filter that would perform a search in the data table for the column 'Supplier' where filter values will be AAA or BBB or CCC a separate value. Creating additional rows by exploding the supplier column is not an option in my case. 

 

Thanks in advance!

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    One sample for your reference.

     

    1. Create a calculated table as below and add a new calculated column in it.

     

    Table = VALUES(Table1[Supplier])
    SP = RIGHT('Table'[Supplier],3)

    2. Then we can create a measure as below.

     

    Measure =
VAR _SELE =
    SELECTEDVALUE ( 'Table'[SP] )
RETURN
    CALCULATE (
        SUM ( Table1[Product] ),
        FILTER ( Table1, SEARCH ( _SELE, Table1[Supplier], 1, 0 ) > 0 )
    )

     

    Please check the pbix as attached.

     

    Regards,

    Frank

  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

     

    Regards,
    Frank

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      What would be an example of the 'Your Measure'? I understand that I need to create this measure first that will contain all possible values in the Supplier tab? For that I created a separate table with all possible values listed as separate rows in a column that I can use but I am not sure ho to apply it in this formula. 

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi Anonymous,

         

        One sample for your reference.

         

        1. Create a calculated table as below and add a new calculated column in it.

         

        Table = VALUES(Table1[Supplier])
        SP = RIGHT('Table'[Supplier],3)

        2. Then we can create a measure as below.

         

        Measure =
VAR _SELE =
    SELECTEDVALUE ( 'Table'[SP] )
RETURN
    CALCULATE (
        SUM ( Table1[Product] ),
        FILTER ( Table1, SEARCH ( _SELE, Table1[Supplier], 1, 0 ) > 0 )
    )

         

        Please check the pbix as attached.

         

        Regards,

        Frank