Forum Discussion

Del235's avatar
Del235
Icon for Helper III rankHelper III
8 months ago
Solved

Advanced filters

I have a list of email addresses.  I want to filter out addresses that start with no-email, gmail and sbcglobal.  How would I setup the Advanced Filter in Power BI? 
Need Help
  • KarinSzilagyi's avatar
    KarinSzilagyi
    8 months ago

    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.
krishnakanth240's avatar
krishnakanth240
Icon for Super User rankSuper User
8 months ago

Hi Del235 

 

> Option 1: Advanced Filter (Recommended – No DAX)

Select your Table / Visual

Go to the Filters pane

Drag the Email Address column into Filters on this visual

Change filter type to Advanced filtering

Set conditions as:

Show items when value:
does not start with no-email
AND
does not start with gmail
AND
does not start with sbcglobal


📌Make sure you use AND, not OR
📌This keeps only emails that do not start with those values

✔ Example:

[email protected]
[email protected]

x Example removed:
[email protected]
[email protected]
[email protected]

 

> Option 2: DAX Calculated Column (More Control)

Use this if you want the logic reusable across visuals.

Valid Email =
IF (
LEFT ( LOWER ( Table[Email] ), 8 ) = "no-email"
|| LEFT ( LOWER ( Table[Email] ), 5 ) = "gmail"
|| LEFT ( LOWER ( Table[Email] ), 9 ) = "sbcglobal",
"Exclude",
"Include"
)


Then filter:

Valid Email = Include

 

> Option 3: Power Query (Best for Large Datasets)

Open Transform Data

Select Email column

Filter → Text Filters → Does Not Begin With

Apply three filters:

does not begin with no-email

does not begin with gmail

does not begin with sbcglobal

Power Query is best for performance and data cleansing

 

Please give headsup / mark it as a solution once it is comepleted. Thank You!