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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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