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 s...
  • 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

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]))
)