Forum Discussion
AlexisOlson
Super User
1 year agoNo CALCULATE Challenge -- Round #2b
tamerj1 provided an ingenious solution to my No CALCULATE Challenge -- Round #2. However, I couldn't easily figure out how to adapt it to a situation where the measure is non-additive and the filteri...
- 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.
lbendlin
Super User
1 year ago
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.
I find this rather alarming. This is something that jeffrey_wang should be made aware of.
jeffrey_wang
Power BI Team
1 year agoThanks for letting me know about the SummarizeColumns bug. We'll look into this.