Multiple filter selected to show zero a value

[GR83]

I have a filter on page 

with fields 

State 

City

if I select any one or multiple cities in static list (NYC, Rochester, Buffalo)

i need sum  total of sale  should be zero else actual sum of sales.

if I select New York State the sales should be total actual sale.

i tried 

IF(selectedvalue(city)) in {NYC,Rochester, Buffalo), 0, 

Calculate(sum(sale))

I understand that selected value give only one value hence when multiple cities in the list are selected it gives actual sale rather than 0.

how can I do this ?

thanks

[bhanu_gautam · Accepted solution]

@GR83 , Try using

DAX
Sales Measure =
IF (
ISFILTERED('Table'[City]) &&
(
CONTAINSSTRING(CONCATENATEX('Table', 'Table'[City], ","), "NYC") ||
CONTAINSSTRING(CONCATENATEX('Table', 'Table'[City], ","), "Rochester") ||
CONTAINSSTRING(CONCATENATEX('Table', 'Table'[City], ","), "Buffalo")
),
0,
CALCULATE(SUM('Table'[Sale]))
)

[Anonymous · Accepted solution]

Hi @GR83 

@bhanu_gautam 's solution should work. The use of ISFILTERED is very smart. 

In addition, here is another measure you can try, not very smart, because the part 

COUNTROWS(VALUES('Table'[City])) < CALCULATE(DISTINCTCOUNT('Table'[City]),ALL('Table'[City])) does the same thing as ISFILTERED('Table'[City]) but makes the formula longer. 

Sales Measure = 
IF(
    COUNTROWS(INTERSECT(VALUES('Table'[City]),{"NYC", "Rochester", "Buffalo"})) > 0 
    && COUNTROWS(VALUES('Table'[City])) < CALCULATE(DISTINCTCOUNT('Table'[City]),ALL('Table'[City])), 
0, 
CALCULATE(SUM('Table'[Sale]))
)

Best Regards,
Jing
Community Support Team

[bhanu_gautam · Accepted solution]

@GR83 , Try using

DAX
Sales Measure =
IF (
ISFILTERED('Table'[City]) &&
(
CONTAINSSTRING(CONCATENATEX('Table', 'Table'[City], ","), "NYC") ||
CONTAINSSTRING(CONCATENATEX('Table', 'Table'[City], ","), "Rochester") ||
CONTAINSSTRING(CONCATENATEX('Table', 'Table'[City], ","), "Buffalo")
),
0,
CALCULATE(SUM('Table'[Sale]))
)