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 User
DONCB =
VAR b = SUMMARIZECOLUMNS(
Geography[RegionCountryName],
FILTER(
ALL(Geography[RegionCountryName]),
[RegionCountryName] <> "China"
),
"do", [Distinct Orders]
)
RETURN
SUMX(
b,
[do]
)Looking at the results they seem to be off though. China accounts for 5.663 distinct order keys but the value without China only goes down by 1399.
I think a more appropriate measure would be
DONCB =
var b = SUMMARIZECOLUMNS(Geography[RegionCountryName],FILTER(ALLSELECTED(Geography[RegionCountryName]),[RegionCountryName]<>"China"),"do",DISTINCTCOUNT(Sales[OrderKey]))
return sumx(b,[do])
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):
- lbendlin
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_wangPower BI Team
Thanks for letting me know about the SummarizeColumns bug. We'll look into this.
- lbendlin
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])