Forum Discussion

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
1 year 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
    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])

     

     

     

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

     

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
1 year ago

AlexisOlson 
In fact the following formula produces the exact same query plan that the CALCULATE does.

 

Distinct Orders (Non-China Benchmark) TJ2 = 
SUMMARIZECOLUMNS ( 
    FILTER ( 
        ALL ( Geography[RegionCountryName] ),
        Geography[RegionCountryName] <> "China" 
    ),
    "@Count", DISTINCTCOUNT ( Sales[OrderKey] )
)

 

The reason why the SUMX formula would work in this case is that there are no common Order Keys that could belong to different regions at the same time. For example, the first shape of your question that was posted in Linkedin was talking about "Color". The SUMX won't work in that case. It can also be simplified as:

 

SUMX (
    FILTER ( 
        ALL ( Geography[RegionCountryName] ),
        Geography[RegionCountryName] <> "China"
    ),
    [Distinct Orders]
)

 

But again that would work only if the each order is not related to more than one value of the filtered column. So that would work in the Store side of the data model wbut won't work in the 'Product' or the Customer side of the data model.