Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
@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.
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.
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.
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
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.
User | Count |
---|---|
89 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |