Forum Discussion
No CALCULATE Challenge -- Round #2b
- 1 year ago
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]) - 1 year ago
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.
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.
- AlexisOlson1 year ago
Super User
tamerj1, yeah I realized that after I saw the SUMX. I accidentally chose a column where OrderKey has only one value per sale rather than a Product filter like I initially mentioned on LinkedIn.
SUMMARIZECOLUMNS does appear to be a viable more generic alternative to CALCULATETABLE and has a very similar syntax structure. If someone can understand SUMMARIZECOLUMNS, then CALCULATE should be no problem.
- tamerj11 year ago
Community Champion
AlexisOlson
In fact I believe that CALCULATE/CLACULATETABLE table simulate the functionality of SUMMARIZECOLUMNS not the opposite.