cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cyberguy23 Frequent Visitor
Frequent Visitor

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Measure for CONTAINS Filter

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

Capture.PNG

 

Please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Measure for CONTAINS Filter

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Measure for CONTAINS Filter

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

Highlighted
Cyberguy23 Frequent Visitor
Frequent Visitor

Re: Measure for CONTAINS Filter

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. 

Community Support Team
Community Support Team

Re: Measure for CONTAINS Filter

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

Capture.PNG

 

Please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Measure for CONTAINS Filter

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.