Forum Discussion

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
2 years ago
Solved

No 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...
  • lbendlin's avatar
    lbendlin
    2 years 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])

     

     

     

  • tamerj1's avatar
    tamerj1
    2 years 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.

     

     

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
2 years ago

Hi, AlexisOlson 

I don't Know is My above question is Valid or not but you can see my Solution

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


Distinct  Order Count Except China

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

 
Ratio 

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


My Output