Filtering Emails

[Del235]

I've created a table that contains email addresses and I don't want to include certain domains.  If I have addresses that appear as johndoe@test.com, I've used an Filter Type : Advanced Filter and Show Items When the Value:  Does Not Contain test.com.

This has no effect.

Why?  How do I filter out test.com?

[InsightsByV]

Hi @Del235

Can you try trimming the column to remove possible whitespace?

CleanEmail = TRIM(CLEAN(LOWER('Table'[Email])))
 
the create a new column:
Test =
NOT CONTAINSSTRING('Table'[CleanEmail], "test.com")

Use Test = True for filtering.

[v-moharafi-msft]

Hi @Del235  ,

We wanted to check if your question has been resolved or if you are still facing any confusion feel free to reach out. 

Thank you.

[Del235]

Its not working for me using the filter/Does not contain

[v-moharafi-msft · Accepted solution]

Hi @Del235 ,

I tried to recreate your scenario in Power BI, and on my end the “Does not contain test.com” filter is working as expected. This usually indicates that the behavior might be related to how the data is formatted rather than the filter logic itself. 

First, you can check for hidden spaces or characters in the email values, as these can sometimes affect filtering. You can validate this by creating a column like Length = LEN(YourTable[Email]) if similar emails return different lengths, it likely means there are trailing spaces or invisible characters. 

Secondly, avoid such issues, you can clean and standardize the data by creating a column like Clean Email = LOWER(TRIM(YourTable[Email])), and then use Is Test Domain = CONTAINSSTRING([Clean Email], "test.com"). Applying the filter on this cleaned column generally resolves these inconsistencies. 

Please find the screenshots below for your reference:

Best Regards, 

Abdul Rafi 

[v-moharafi-msft]

Hi @Del235  ,

Thank you for reaching out to Microsoft Fabric Community and Thanks to @parry2k  for Sharing valuable insights.

Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

 Best Regards,

Abdul Rafi

[parry2k]

@Del235 this should work though, can you create a column and put it in the table to see if you get true or false:

Is Exist = CONTAINSSTRING(YourTable[YourEmailAddressColumn], "test.com")