Forum Discussion
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...
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]))
)
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
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