Filter formula in PowerPivot

[successken]

I have a Database that contains either an email address or typically the words declined, dc, na, none, etc... I was operating within my table =IF(Email="*@*",Email,"") In essence if the email contained @ then transfer the email address over otherwise leave it blank. Is there a filter DAX formula that can accomplish the same result? Thank you in advance.

 

[NickyvV]

You can even use the same formula in a new Calculated Column in your model and use the new column in your pivot table if that makes sense.

HTH

 

[successken]

NickyvV - Thank you for the reply. I believe that is what I am trying to do with no avail.

I am having difficulty finding the right calculated formula that would net me the same result. Currently I have the following formula in an excel table:

=IF(COUNTIF([@emailname],"*@*")>0,[@emailname],"")

Which in turn is updated into a PowerPivot table. I have tried to replicate the formula with a CALCULATE:

=if(CALCULATE(COUNTA (Table_MVE_Multi_Summary_Query[emailname]),Table_MVE_Multi_Summary_Query[emailname]="*@*")>0,Table_MVE_Multi_Summary_Query[emailname],"")

With it only returning blanks.

 

[NickyvV]

Hi,

I managed to solve the problem with the following formula:

=IF(ISERROR(SEARCH("@"; Table1[emailname])); BLANK(); Table1[emailname])

SEARCH is case-insensitive, you could use FIND if you need case-sensitivity.
Below an example of the Calculated Column:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH]name[/TH]
[TH]emailname[/TH]
[TH]NewColumn[/TH]
[/TR]
[TR]
[TD]pete[/TD]
[TD]pete.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]peter[/TD]
[TD]peter@doe.com[/TD]
[TD]peter@doe.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[successken]

This works great!! Thank you very much for your help with this.