TEXT CONTAINS filter for list of multiple strings

[omillzy]

In SalesForce we have a pick-list for the Contact Type. The possible values are:

Contact Type Filter

Billing Contact

In Life Property Contact

Sales Contact

Out of Hours Contact

Legal Contact

Project Management Contact

Director Contact

Remittance - Finance

Statement - Finance

Purchase Ledger - Finance

Business Support Contact

SalesForce spits this value out into a single cell as a concatenation of whatever the client picked for the contact type, here's a sample of the value: 

Contact_Type__c
Billing Contact;Remittance - Finance;Statement - Finance
Director Contact
Billing Contact;Remittance - Finance;Statement - Finance
Remittance - Finance;Statement - Finance
Legal Contact
Billing Contact;In Life Property Contact;Remittance - Finance;Statement - Finance
Billing Contact;Remittance - Finance;Statement - Finance
Director Contact
In Life Property Contact;Out of Hours Contact
Remittance - Finance
Out of Hours Contact
Billing Contact;In Life Property Contact;Sales Contact;Out of Hours Contact;Legal Contact;Project Management Contact;Director Contact;Remittance - Finance;Statement - Finance
Out of Hours Contact
In Life Property Contact
In Life Property Contact;Out of Hours Contact;Legal Contact
Remittance - Finance
Billing Contact;In Life Property Contact;Remittance - Finance;Statement - Finance
Billing Contact
Billing Contact;Remittance - Finance;Statement - Finance
Billing Contact;In Life Property Contact;Sales Contact;Out of Hours Contact;Director Contact
In Life Property Contact
In Life Property Contact;Legal Contact
Billing Contact;In Life Property Contact;Remittance - Finance;Statement - Finance
Billing Contact;Remittance - Finance;Statement - Finance
Director Contact

Basically, I want the user to be able to select one of the values in a filter, and the table filter if the cell contains that contact type (almost like a grep command in regex)

I've found the following, which in theory is what I need, but the DAX seems to just run for eternity until I run out of RAM (I have 32gb)

IsFiltered = 
IF(
    SUMX('Contact Type Filter',
    FIND(
        'Contact Type Filter'[Filter Queries],
        MAX(Contact[Contact_Type__c]),,0))>0,
        "True",
        "False"
)

I'm then putting the 'IsFiltered' as a visual filter, and creating a dropdown using my 'Contact Type Filter' list

I'm guessing my dataset is too large for the SUMX(), or there  might be better ways to achieve what I want - such as using SELECTEDVALUE()

Any help/tips much appreciated

[parry2k]

@omillzy may be the best option is to split the contact_type_c in rows in power query and then set relationship between filter table and contact_type_c table and everything will work as expected.

[omillzy]

Cheers @parry2k ,That was my initial approach, but I ended up with lots of repeating rows (or a very wide dataset if I split by delimiter) - the table holds details about customers including their contact details so I don't really want to repeat this information.

Ideally, I want to keep the picklist values in the single-cell and do a 'text contains' solution if it is possible.

[omillzy · Accepted solution]

Figured it out if anyone is interested

Measure = 
COUNTROWS (
    FILTER (
        Contact,
        CONTAINSSTRING (
            Contact[Contact_Type__c],
            SELECTEDVALUE ( 'Contact Type Filter'[Filter Queries] )
        )
    )
)

Then I just applied a visual filter where 'Measure is 1' and chucked the contact picklist in a filter works much faster than the SUMX() alternative

[DvdH]

Hey!
Im currently trying to do something simular i think.
I am trying to choose multiple user inputted values as a filter/slicer.
i.e
Table: Test
Column: Letters
Column rows:
A
B
C all the way to Z
User text input(Sepperated by spaces only): A B C D E F G
Preferred outcome. Show rows which include A B C D E F G.

I have multiple columns and im bassicly trying to sort by multiple user inputted ID's.