Forum Discussion
Advanced filters
Hi Del235, where exactly do you want to filter them out? On page level, inside a measure, or in Power Query already?
Power Query:if Text.StartsWith(Text.Lower([Email]), "no-email") or Text.StartsWith(Text.Lower([Email]), "gmail") or Text.StartsWith(Text.Lower([Email]), "sbcglobal") then 0 else 1- You can then either filter the rows in Power Query already for [YourColumnName] = 1 or apply it as a filter via the filter pane after loading it into your model.
DAX (assuming that you literally mean that those should be the first few characters in the column and you know that there won't be any other characters before the parts you're looking for):
IsValidEmail = IF ( LEFT ( LOWER ( 'Table'[Email] ), 8 ) = "no-email" || LEFT ( LOWER ( 'Table'[Email] ), 5 ) = "gmail" || LEFT ( LOWER ( 'Table'[Email] ), 9 ) = "sbcglobal", 0, 1 )
As a Filter in the Filter-Pane:- Switch column to "Advanced filtering" and use "does not start with" (you can drag + drop the same column multiple times into the filter pane to apply it for all three terms
OR - Create a Measure with the DAX-Example above, drag it into the filter pane and filter for IsValidEmail = 1.
- You can then either filter the rows in Power Query already for [YourColumnName] = 1 or apply it as a filter via the filter pane after loading it into your model.
Hi Del235 ,
This can be done cleanly in Power BI, and the best approach depends on whether you want it at data-load time or report-interaction time.
✅ Recommended approach (Power Query – best practice)
If these emails should always be excluded, do this in Power Query.
Steps
Open Transform data
Select the column with the email addresses
Go to Home → Filter → Text Filters → Does Not Begin With
Add the following conditions:
does not begin with no-email
does not begin with gmail
does not begin with sbcglobal
Combine them using AND
This permanently removes those records from the model.
Equivalent M code
= Table.SelectRows(
Source,
each not (
Text.StartsWith([Email], "no-email")
or Text.StartsWith([Email], "gmail")
or Text.StartsWith([Email], "sbcglobal")
)
)
🔁 Alternative: Advanced Filter in the report (no data removal)
If you want to keep the data but filter it visually:
Select the visual
Go to the Filters pane
Add the Email field
Choose Advanced filtering
Configure:
does not begin with no-email
AND does not begin with gmail
AND does not begin with sbcglobal
This affects only the visual or page, not the dataset.
❌ What not to do
Avoid using CONTAINS or SEARCH for this case
Avoid creating calculated columns unless absolutely necessary
Don’t rely on slicers for exclusion logic like this
Conceptual illustration
Email List
├── [email protected] ❌ filtered out
├── [email protected] ❌ filtered out
├── sbcglobal_user@... ❌ filtered out
└── [email protected] ✅ kept
Summary
✔ Use Power Query if the rule is permanent
✔ Use Advanced Filters if it’s report-level logic
✔ Use Does Not Begin With + AND conditions
If this helped, please consider giving a kudos 👍
And if it solved your problem, feel free to mark this as the Accepted Answer ✔