No CALCULATE Challenge -- Round #2b

[AlexisOlson]

@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 filtering I want to do is not a single value from a column.

In this case, I want to recreate this deceptively simple measure:

Distinct Orders (Non-China Benchmark) = 
CALCULATE (
    DISTINCTCOUNT ( Sales[OrderKey] ),
    Geography[RegionCountryName] <> "China"
)

For motivation purposes, assume China is an outlier in my data that I want to ignore for benchmarking purposes.

Note that this probably isn't something I'd use on its own. It would likely be used as a denominator in another measure like this:

Distinct Order Ratio =
DIVIDE (
    [Distinct Orders],
    CALCULATE (
        [Distinct Orders],
        Geography[RegionCountryName] <> "China"
    )
)

For full credit:

1. CALCULATE and CALCULATETABLE are not allowed.
2. The measure should work in any reasonable filter context, not just for the particular sample setup.
3. Ideally, the measure should also be robust to model adjustments as discussed previously.

The primary goal is to find any solution.

The secondary goal is to find one that can be grokked by a non-expert.

Sample screenshot: 

Sample PBIX file attached.

Prior participants: @SharmaAntriksh, @Greg_Deckler, @CNENFRNL, @ValtteriN

Other friends: @BA_Pete, @bcdobbs, @smpa01, @parry2k

[lbendlin · Accepted solution]

 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])

[jeffrey_wang]

Thanks for letting me know about the SummarizeColumns bug. We'll look into this.

[lbendlin · Accepted solution]

 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]

@lbendlin, @Dangar332 There's a visual level filter excluding a few countries, which accounts for the missing orders. It's working as intended even though it's a bit weird in this toy scenario.

[Dangar332]

HI, @AlexisOlson 

Measure for Not taking Order count of China which are use in another for Taking Total. 

Measure = 
 var a = 
COUNTROWS(SUMMARIZECOLUMNS(
		Sales[OrderKey],
		FILTER(
			VALUES(Geography[RegionCountryName]),
			Geography[RegionCountryName] <> "China"
		),"sf",COUNTROWS(Sales)
	))
 RETURN
a

Measure for Distinct Order (Non-China Benchmark)

Distinct Order (Non-China Benchmark)_Dangar= 
SUMX(
  ALL(
    Geography[RegionCountryName]),
    [Measure]
  )

Retio

Distinct Order Ratio_ = 
DIVIDE([Distinct Orders],[Distinct Order (Non-China Benchmark)_Dangar])