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

[tamerj1]

@AlexisOlson 
Here is a NOCALCULATE solution the way @Greg_Deckler preffers.

Distinct Orders (Non-China Benchmark) TJ3 = 
COUNTROWS (
    GROUPBY ( 
        FILTER ( 
            CROSSJOIN ( 
                ALLSELECTED ( Sales[OrderKey] ), 
                FILTER ( 
                    ALL ( Geography[RegionCountryName] ),
                    'Geography'[RegionCountryName] <> "China" 
                )
            ),
            NOT ISBLANK ( [Sales Amount] )
        ),
        Sales[OrderKey]
    )
)

[tamerj1]

@AlexisOlson & @lbendlin 

Can you please explain further the SUMMARIZECOLUMNS bug that you have encountered. I've failed so far to reproduce it.

[AlexisOlson]

@tamerj1, I see this when I define [DONCB] and add it to the matrix (nothing weird so far).

Then I clear the visual filter on RegionCountryName (still no bug).

Now when I select all but Armenia from that same visual filter, something breaks:

Because it's clearly a bug, it's possible it may depend on the specific version of Power BI you have installed and might not behave exactly the same way on your system.

[tamerj1]

@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.

[tamerj1 · Accepted solution]

@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.

[AlexisOlson]

@tamerj1, yeah I realized that after I saw the SUMX. I accidentally chose a column where OrderKey has only one value per sale rather than a Product filter like I initially mentioned on LinkedIn.

SUMMARIZECOLUMNS does appear to be a viable more generic alternative to CALCULATETABLE and has a very similar syntax structure. If someone can understand SUMMARIZECOLUMNS, then CALCULATE should be no problem.

[tamerj1]

@AlexisOlson 
In fact I believe that CALCULATE/CLACULATETABLE table simulate the functionality of SUMMARIZECOLUMNS not the opposite. 

[Greg_Deckler]

@AlexisOlson I don't believe that the CALCULATE formula is returning the correct results. Here's why. If I add up all of the non-China distinct counts in Economy for Asia that are not China then I get 23,925. This would be a maximum number but the CALCULATE formula returns 28,189. There's no way to get that number really without adding in China's numbers to get you to 29,588 and then probably have some duplicates maybe? The point is though, I don't see how that number is possible quite frankly without ignoring the Filters pane filters for RegionCountryName which I'm not sure is or is not correct/intended.

Therefore, I would first like to ask that you prove that the 28,189 number is, in fact, correct and not something being made up by CALCULATE. Is it intended that it preserve all filters but ignore the filters in the Filters pane for RegionCountryName?

The solution would be the following for how the CALCULATE is working, which I feel isn't correct or at least extremely non-intuitive for the end user. I want to credit @lbendlin here as this measure is based off of his work.

DONC_D = 
    VAR __Continent = MAX( 'Geography'[ContinentName] )
    VAR b = 
        SUMMARIZE(
            FILTER(
                ALL( Geography),
                [ContinentName] = __Continent && [RegionCountryName] <> "China"
            ),
            'Geography'[RegionCountryName],
            "do", DISTINCTCOUNT( Sales[OrderKey] )
        )
RETURN
    SUMX( b,[do] )

[AlexisOlson]

@Greg_Deckler, the distinct count for Asia Economy without any visual filter is 33,852. The distinct count for China Economy is 5,663. The difference is 28,189, exactly as intended. I really do want to keep the things filtered out in the visual filter in my real-life measure that motivated this contrived example.

Your measure is close but fails on the grand total line.

@lbendlin, I don't think even the most extreme No CALCULATE proponents would go so far as to ban measures altogether.

Your updated measure appears to work as intended so I've accepted it as a solution.

I realize now that I didn't think carefully enough when designing the problem. In my actual application, the measure isn't additive along any dimension, so DISTINCTCOUNT isn't nonadditive enough to solve what I'm really after.

This is a bit closer to what I'm trying to solve and might be more intuitive even though it's slightly more complex:

Median Markup =
MEDIANX ( Sales, -1 + Sales[SalesAmount] / Sales[TotalCost] )

Markup Non-Deluxe Ratio =
DIVIDE (
    [Median Markup],
    CALCULATE ( [Median Markup], 'Product'[ClassName] <> "Deluxe" )
)

Just like in my post, it should be unsurprising that the denominator is the same regardless of the ClassName selected via slicers or visual filters.

Consider this Round #2c. Ideally, the solution should be general enough that it works for any similar measure, not just the [Median Markup] example, just like it does with CALCULATE.

[Greg_Deckler]

@AlexisOlson Here is another version that is twice as fast and almost as fast as CALCULATE:

MRNC = 
    VAR __Stores = SUMMARIZE( 'Stores', [StoreKey] )
    VAR __Products = SUMMARIZE( FILTER( ALL( 'Product' ), [ClassName] <> "Deluxe" ), [ProductKey] )
    VAR __Table = 
        NATURALLEFTOUTERJOIN(
            NATURALLEFTOUTERJOIN(
                __Stores,
                ALLSELECTED( 'Sales' )
            ),
            __Products
        )
    VAR __Divisor = MEDIANX( __Table, -1 + [SalesAmount] / [TotalCost] )
    VAR __Numerator = [Median Markup]
    VAR __Result = DIVIDE( __Numerator, __Divisor )
RETURN
    __Result

[Greg_Deckler]

@AlexisOlson This solution appears to work when used in the same matrix as the 2b example. Including the total. Doubt it is the most efficient but seems to work.

Measure = 
    VAR __Stores = SUMMARIZE( 'Stores', [StoreKey] )
    VAR __Products = SUMMARIZE( FILTER( ALL('Product'), [ClassName] <> "Deluxe" ), [ProductKey] )
    VAR __Divisor = MEDIANX( FILTER( ALLSELECTED('Sales'), 'Sales'[StoreKey] IN __Stores && 'Sales'[ProductKey] IN __Products ), -1 + 'Sales'[SalesAmount] / 'Sales'[TotalCost] )
    VAR __Numerator = [Median Markup]
    VAR __Result = DIVIDE( [Median Markup], __Divisor )
RETURN
    __Result

[Greg_Deckler]

@AlexisOlson So is are these measures used in the same matrix visual or a different visual?

[AlexisOlson]

Here's a visual to check against (no slicer or visual filters applied). I've included my [Markup Non-Deluxe Ratio] measure along with both versions you've suggested so far.

[Greg_Deckler]

@AlexisOlson The fixing of the grand total is a very straight forward solve. Jusy yet another example of incorrect measure totals in Power BI. Honestly, Power BI gets the total wrong so often, I don't even pay attention to it any longer just assuming that it is wrong as I typically turn that garbage off anyway.

[AlexisOlson]

Because SUMMARIZECOLUMNS doesn't (yet) seem reliable inside a measure, I'd prefer alternative approaches if they can be found. (It's only recently that this function doesn't throw an error when used like this.)

Also, SUMMARIZECOLUMNS is like CALCULATETABLE but arguably even more complicated, so IMO, it violates the spirit of the No CALCULATE ideology even though I didn't explicitly exclude it.

[Dangar332]

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 

[Dangar332]

Hi, @AlexisOlson 

I have one question 
Is the Same OrderKey present for Different RegionCountryName? Why Order key 29588 is the count for Economy class and Asia; if we do not take into account the order key for China, it becomes 29588-5663 = 23925; nonetheless, in your situation, it displays 28189.

[lbendlin]

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

[AlexisOlson]

@lbendlin, 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.