topic Re: No CALCULATE Challenge -- Round #2b in DAX Commands and Tips

No CALCULATE Challenge -- Round #2b

AlexisOlson — Fri, 11 Oct 2024 20:05:00 GMT

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:

CALCULATE and CALCULATETABLE are not allowed.
The measure should work in any reasonable filter context, not just for the particular sample setup.
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

Re: No CALCULATE Challenge -- Round #2b

lbendlin — Fri, 11 Oct 2024 21:38:58 GMT

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

Re: No CALCULATE Challenge -- Round #2b

Dangar332 — Fri, 11 Oct 2024 21:47:12 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

Dangar332 — Fri, 11 Oct 2024 22:00:27 GMT

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

Re: No CALCULATE Challenge -- Round #2b

AlexisOlson — Fri, 11 Oct 2024 22:50:49 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

AlexisOlson — Fri, 11 Oct 2024 23:12:07 GMT

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

Re: No CALCULATE Challenge -- Round #2b

AlexisOlson — Fri, 11 Oct 2024 23:29:44 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

Greg_Deckler — Sat, 12 Oct 2024 01:43:49 GMT

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

Re: No CALCULATE Challenge -- Round #2b

lbendlin — Sat, 12 Oct 2024 00:33:34 GMT

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

Re: No CALCULATE Challenge -- Round #2b

AlexisOlson — Sat, 12 Oct 2024 03:37:17 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

tamerj1 — Sat, 12 Oct 2024 05:18:49 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

Dangar332 — Sat, 12 Oct 2024 08:22:24 GMT

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

Re: No CALCULATE Challenge -- Round #2b

Greg_Deckler — Sat, 12 Oct 2024 14:45:48 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

Greg_Deckler — Sat, 12 Oct 2024 14:58:56 GMT

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

Re: No CALCULATE Challenge -- Round #2b

Greg_Deckler — Sat, 12 Oct 2024 15:29:38 GMT

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

Re: No CALCULATE Challenge -- Round #2b

lbendlin — Sat, 12 Oct 2024 17:31:03 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

Greg_Deckler — Sat, 12 Oct 2024 21:13:55 GMT

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

Re: No CALCULATE Challenge -- Round #2b

jeffrey_wang — Sun, 13 Oct 2024 03:52:09 GMT

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

Re: No CALCULATE Challenge -- Round #2b

AlexisOlson — Sun, 13 Oct 2024 15:31:42 GMT

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.

Re: No CALCULATE Challenge -- Round #2b

tamerj1 — Mon, 14 Oct 2024 02:41:05 GMT

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.