Forum Discussion
Filter out multiple values from a list
Hello,
I have a table that containts a list of all contacts that I have exported from our CRM (salesforce.com) that I want to cleanup.
I am looking to filter the list to remove all contacts that have a an email from a public domain (gmail, hotmail, yahoo, ect.) and keep only the ones with company emails.
I don't want to use a slicer and go trought the list of emails manually as there are over 15,000 contacts in that list.
I managed to exclude one domain by using the page level/report level filters and keeping only the records for which the email adress did no contain "@gmail" but I can only do it for one so I am left with all the other public domains.
I also tried creating a new column by splitting the email column using the "@" as delimiter and then using a slicer. This reduced the size of the list but there are still over 500 different domains to pick from so this option is far from ideal.
Does anyone have a idea on how to filter out/exclude multiple values at a time?
Thanks in advance,
Phil
Hi Anonymous ,
As I said, you can add public domain table with domain text which you want to exclude.
Then add a calculated column to contact table to compare current row content with public domain table list.
Is Public = COUNTROWS ( FILTER ( ALL ( 'Pubich Domain' ), SEARCH( 'Pubich Domain'[Domain], [Email], 1, -1 ) > 0 ) ) > 0Regards,
Xiaoxin Sheng
7 Replies
Hi Anonymous,
Please find a short example of my data below, note that contact information is fake and not valid.
Table Name: Contact
First Name Last Name Email Phone job title Company John Doe [email protected] 555-555-5555 Director of Marketing Sony Jenny Brooks [email protected] Peter Parker [email protected] 555-555-5555 Consultant Peter Inc. Susan James [email protected] 555-555-5555 Student Kim Sullivan [email protected] Analyst HP Brenda Wozniak [email protected] 555-555-5555 Lexus Scott Wade [email protected] 555-555-5555 VP Customer services UPS Natalia hernandez [email protected] 555-555-5555 Alex Rawlings AlexRaw@aol Customer support agent Ford Patrick Kane [email protected] CX Director Verizon As with any other contact database not all fields are populated with prevents me from filtering on other values like company or title. The only required field to enter a new record in the table was an email address so filtering on the email seems like the only way to properly clean that database.
Ultimately, I want to get a table with excluding all record with a public email
Also, since in some rare occasions some public email might be relevant if other criterias are meet, I would prefer to apply the filter at the report level and not at the initial query level when importing the data from salesforce.com
I hope this can help you better understand what I am trying to get to and define the right query/measure
Hi Anonymous ,
As I said, you can add public domain table with domain text which you want to exclude.
Then add a calculated column to contact table to compare current row content with public domain table list.
Is Public = COUNTROWS ( FILTER ( ALL ( 'Pubich Domain' ), SEARCH( 'Pubich Domain'[Domain], [Email], 1, -1 ) > 0 ) ) > 0Regards,
Xiaoxin Sheng
Hi. As a workaround, maybe you can try to test your connection with a 3rd party connector instead of manually exporting data from SF. I currently use Windsor.ai and I can directly pull contacts data from SF and I can even exclude some values directly on Windsor interface using the filters option. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:
After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:
There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.
Create a separate table of public email domains, extract and normalize the domain from each Contact email, and create an Is Public Email flag using an exact domain match. You can then filter this flag at the report level while keeping individual exceptions when needed. This is safer than searching for domain text anywhere inside the full email address.
If you are importing the Contacts from Salesforce, Power BI Connector for Salesforce by Metrica Software can help you select only the Contact fields needed for the model while keeping all contact rows available for report-level filtering: https://appexchange.salesforce.com/appxListingDetail?listingId=31526f0e-abd8-4cb5-bd1a-3bd56b5c0577
