Help with a calculated field in a PivotTable

ben_project 

In addition to Wyn Hopkins . I have practically now experience in using calculating fields with texts and can't say why such combination doesn't work and what are workarounds if any. You may test much more simple calculated field

= LEN(City)

it always returns 1.

If to work with data model and you would like to use SEARCH or FIND to recognize, for example, "City of New York" as NY, these functions work bit different way compare to Excel. They have 4th parameter to return if_not_found value. Without details, if don't use you'll receive an error for entire column/measure.

In data model I'd add calculated column

as

=IF(SEARCH("New York",Table1[City],,0),1,0)

or

=MIN(SEARCH("New York",Table1[City],,0),1)

If with measure you may check filter context as Wyn suggested or iterate row context wit ...X functions as

IsNY:=IF(SUMX(Table1,SEARCH("New York",Table1[City],,0)),1,0)

That looks like