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
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
@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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
90 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |