Forum Discussion

GR83's avatar
GR83
Regular Visitor
1 year ago
Solved

Multiple filter selected to show zero a value

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's avatar
    bhanu_gautam
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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