Measure for CONTAINS Filter

[Cyberguy23]

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

Product	Supplier
123	AAA
234	AAA;BBB
345	AAA;CCC
567	AAA;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!

[v-frfei-msft · Accepted solution]

Hi @Cyberguy23,

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

[Zubair_Muhammad]

@Cyberguy23

Try using the search function in DAX

https://docs.microsoft.com/en-us/dax/search-function-dax

Something on these lines

Calculate([Your Measure],Filter(Table,search(selectedvalue(slicerfield),Table[Sullplier],1,0)>0))

[Cyberguy23]

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 · Accepted solution]

Hi @Cyberguy23,

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 · Accepted solution]

Hi @Cyberguy23 ,

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

Regards,
Frank