Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

TEXT CONTAINS filter for list of multiple strings

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@Anonymous 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.