PowerPivot DAX Measure Error

[melq64]

Hi,

I created a measure to countrows using search to return part of a text which is working fine but I wan't to add to this measure to also count values from another column based on criteria that it does not equal blank. Here is the dax formula:

TotalPOBoxAccepScan:=CALCULATE(COUNTROWS(FILTER('DistProfile v1-3',SEARCH("PO BOX",'DistProfile v1-3'[Dest_Address],1,0)>0))&&FILTER('DistProfile v1-3','DistProfile v1-3'[Acceptance]<>BLANK()))

The error I get is "Semantic Error: The expression refers to multiple columns. Multiple columns cannot convert to a scalar value"

Appreciate any feedback.

Thanks

 

[brawnystaff]

Using previous example where it is called Table1, DAX would look like this:

=CALCULATE(COUNTROWS(Table1),Filter(Table1,if(Table1[Acceptance]<>BLANK(),Search("PO BOX",Table1[Address],1,0)>0)))

https://drive.google.com/open?id=1uVMsKCGErlMZmW-SKMdZxRWrFNdFHl4X

 

[brawnystaff]

Was able to shorten it and get rid of the If statement using following syntax:

=CALCULATE(COUNTROWS(Table1),Filter(Table1,Table1[Acceptance]<>BLANK()&&Search("PO BOX",Table1[Address],1,0)>0))

 

[melq64]

Thanks that worked but just realized my blank values are represented by "No Scan" so i tried replacing blank with "No Scan" but got error saying "cannot compare values of type date with type text, consider using the Value or Format function to convert..."

 

[brawnystaff]

Was able to change on my end with both DAX formulas with no problems. Maybe a formatting issue with your data.

 

[melq64]

Hmm..must be my column format which is custom date format. Will have a play. Thanks again for your help, appreciate it