Forum Discussion
Super UserNo CALCULATE Challenge -- Round #2b
inside a measure,Note that measures implicitly use CALCULATE, somewhat negating your premise.
Having said that, try this version
DONCB = VAR b = ADDCOLUMNS(ALL(Geography[RegionCountryName]),"do", [Distinct Orders]) RETURN SUMX(FILTER(b,[RegionCountryName]<>"China"),[do])AlexisOlson
Here is a solution using SUMMARIZECOLUMNS.Distinct Orders (Non-China Benchmark) TJ = COUNTROWS ( SUMMARIZECOLUMNS ( Sales[OrderKey], FILTER ( ALL ( Geography[RegionCountryName] ), Geography[RegionCountryName] <> "China" ), "@Count", COUNTROWS ( Sales ) ) )However, applying the same filter over a higher cardinality column will reveal the difference with the SUMX solution provided by lbendlin . DISTINCTCOUNT is non-additive calculation, which means SUMX will result in wrong results.
Super Userlbendlin, this seems to work sometimes but other times breaks for reasons I don't understand. Something seems buggy about SUMMARIZECOLUMNS inside a measure.
I can reliably reproduce the problem by opening the file, defining [DONCB], adding it to the matrix visual (by selecting the matrix and clicking the checkbox next to the measure name in the Data pane), clearing the RegionCountryName visual filter, and then selecting all but one country (say, Armenia) in that same visual filter.
Here's (a piece of) what it looks like when I follow exactly these steps (and no others):
Super User
inside a measure,
Note that measures implicitly use CALCULATE, somewhat negating your premise.
Having said that, try this version
DONCB =
VAR b = ADDCOLUMNS(ALL(Geography[RegionCountryName]),"do", [Distinct Orders])
RETURN SUMX(FILTER(b,[RegionCountryName]<>"China"),[do])