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

No CALCULATE Challenge -- Round #2

AlexisOlson — Fri, 14 Apr 2023 17:30:29 GMT

I learned a lot from the brilliant folks who participated in my previous challenge, so I wanted to explore another situation where I find CALCULATE highly useful and see how others manage to do without it.

In this scenario, I want to define a benchmark ratio as the ratio of sales to sales in North American Stores, preserving all filtering other than geography and channel. Using CALCULATE, this is simple:

Sales Benchmark Ratio = VAR _BenchmarkSales = CALCULATE ( [Sales Amount], ALL ( Geography ), Geography[ContinentName] = "North America", ALL ( Channel ), Channel[ChannelName] = "Store" ) VAR _Ratio = DIVIDE ( [Sales Amount], _BenchmarkSales ) RETURN _Ratio

The challenge is to write a measure without CALCULATE that can reproduce this robustly.

Here's an example of what the values should be with some filters applied:

For full credit, the measure should work regardless of what dimensions are used for slicers and what dimensions are used for rows and columns of the matrix. It should still work even if new dimensions are added to the data model.

Participants from last time:

tamerj1 AntrikshSharma Greg_Deckler Anonymous

Other friends who might like to join:

BA_Pete bcdobbs CNENFRNL smpa01 parry2k ValtteriN

Re: No CALCULATE Challenge -- Round #2

tamerj1 — Fri, 14 Apr 2023 16:23:31 GMT

Hi AlexisOlson

Doesn't seem that simple 😅

it's 8:15pm right now here and I don't have access to my laptop. I hope not to close the subject until tomorrow same time. I will try to give it a shot anyway from my phone.
I just wanted to say that even this CALCULATE version of the measure won't work in all scenarios. For example if you have Country as a slicer or if you have any attribute from the Channel table other than Channel Name in the rows, I guess it won't work. So please be nice and grant a little bit of flexibility 😎

Re: No CALCULATE Challenge -- Round #2

tamerj1 — Fri, 14 Apr 2023 16:46:10 GMT

AlexisOlson

Not the best performance for sure

Sales Benchmark Ratio =
VAR _BenchmarkSales =
SUMX (
FILTER (
ALLSELECTED ( Sales ),
RELATED ( Geography[ContinentName] ) = "North America"
&& RELATED ( Channel[ChannelName] ) = "Store"
),
Sales[Quantity] * Sales[Price]
)
VAR _Ratio =
DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
_Ratio

Re: No CALCULATE Challenge -- Round #2

Anonymous — Fri, 14 Apr 2023 16:48:05 GMT

AlexisOlson - OK, interesting....fair or foul to tinker with the data model and collapse the geography snowflake to simplify?

Re: No CALCULATE Challenge -- Round #2

AlexisOlson — Fri, 14 Apr 2023 17:24:34 GMT

Collapsing the snowflake to a star schema is fine.

Re: No CALCULATE Challenge -- Round #2

Anonymous — Fri, 14 Apr 2023 17:28:35 GMT

Cool, thanks. Would you mind throwing the data file into your original post?

Re: No CALCULATE Challenge -- Round #2

AlexisOlson — Fri, 14 Apr 2023 17:29:54 GMT

Adding a Country slicer doesn't seem to break it as far as I can tell but fair point about the Channel. I didn't use another ALL since there's only one visible column from that table, but I'll go ahead and edit that in.

Re: No CALCULATE Challenge -- Round #2

AlexisOlson — Fri, 14 Apr 2023 17:32:21 GMT

Anonymous I don't have the underlying data files, unfortunately. I downloaded it as a PBIX. 😕

Re: No CALCULATE Challenge -- Round #2

CNENFRNL — Fri, 14 Apr 2023 20:46:32 GMT

Inspiring way addressing this tricky chanllenge; but I'm afraid

FILTER( ALLSELECTED( Sales ), RELATED( Geography[ContinentName] ) = "North America" && RELATED( Channel[ChannelName] ) = "Store" )

would produce an empty table because there exist an external slicers Channel[ChannelName] and Geography[ContinentName]; when Channel[ChannelName]<>"Store" or Geography[ContinentName]<>"North America" are selected, the filtered table is empty.

Re: No CALCULATE Challenge -- Round #2

CNENFRNL — Fri, 14 Apr 2023 21:15:45 GMT

Interesting challenge 👋 Looking forward to some tricky solutions as I've already spent more than half hour but no luck. I really doubt there exists an elegant, error-proof alternative, especially when some new slicers will intervene.

Off the track comment on CALCULATE

I admit that the idea to kick the ass of CALCUALTION is buzzy at the first glance; but with further comprehension of DAX, I'd like to say the mechanism of context transition triggered by CALCULATE is a genius one! It differentiates DAX from other traditional "context insensitive" lauguages like Excel formulas, SQL etc.

Re: No CALCULATE Challenge -- Round #2

AlexisOlson — Sat, 15 Apr 2023 14:48:14 GMT

Yeah, this example demonstrates what I consider the most important use case for CALCULATE -- modifying filter context in a simple and precise way that works in almost any context without needing revision. One can write a measure without CALCULATE that works for a specific visual but if it can't reliably be reused in other contexts, we're back to the bad old days of SSRS where we often need separate queries for each visual.

Much of my day-to-day work is building and maintaining common data models that feed dozens of thin reports (I don't know in advance every possible context that each measure will be used) and this type of situation (where just one or two filters need to be removed or adjusted) comes up frequently with benchmarks and the only way I can think to do it without CALCULATE is remove all filters and specify the ones I want to put back, which requires (as far as I can tell) including within the measure definition any dimension table I ever want to filter or slice on, which is a problem if new dimensions are ever introduced (and probably not great for performance either).

Re: No CALCULATE Challenge -- Round #2

Greg_Deckler — Sat, 15 Apr 2023 16:44:31 GMT

AlexisOlson Technically meets the letter of the law with regard to the challenge. 🙂

Sales Benchmark Ratio NC Cheat = VAR _BenchmarkSalesTable = CALCULATETABLE( 'Sales', ALL ( Geography ), Geography[ContinentName] = "North America", Channel[ChannelName] = "Store" ) VAR _Ratio = DIVIDE ( [Sales Amount 1], SUMX(_BenchmarkSalesTable,[SalesAmount]) ) RETURN _Ratio

Re: No CALCULATE Challenge -- Round #2

Greg_Deckler — Sat, 15 Apr 2023 17:29:38 GMT

AlexisOlson This one works but is slow. Sharing it in case someone has a brilliant idea on how to optimize

Sales Benchmark Ratio NC2 = VAR __Products = DISTINCT('Product'[ProductKey]) VAR __Promotions = DISTINCT(Promotion[PromotionKey]) VAR __GeoKeys = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Geography'), [ContinentName] = "North America"), "GeoKey",[GeographyKey])) VAR __Stores = DISTINCT(SELECTCOLUMNS(FILTER(ALL(Stores), [GeographyKey] IN __GeoKeys),"StoreKey",[StoreKey])) VAR __Channels = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Channel'), [ChannelName] = "Store"),"ChannelKey",[Channel])) VAR __BenchmarkSalesTable = FILTER( FILTER( FILTER( FILTER( ALL('Sales'), [ProductKey] IN __Products ), [PromotionKey] IN __Promotions ), [StoreKey] IN __Stores ), [channelKey] IN __Channels ) VAR __BenchmarkSales = SUMX(__BenchmarkSalesTable, [SalesAmount]) VAR __Ratio = DIVIDE ( [Sales Amount 1], __BenchmarkSales ) RETURN __Ratio

Re: No CALCULATE Challenge -- Round #2

tamerj1 — Sat, 15 Apr 2023 18:07:17 GMT

Greg_Deckler

"It should still work even if new dimensions are added to the data model." said AlexisOlson

Re: No CALCULATE Challenge -- Round #2

AlexisOlson — Sat, 15 Apr 2023 18:45:02 GMT

Greg_Deckler This is exactly the sort of approach I mentioned above and does not qualify as a full solution.

the only way I can think to do it without CALCULATE is remove all filters and specify the ones I want to put back, which requires (as far as I can tell) including within the measure definition any dimension table I ever want to filter or slice on, which is a problem if new dimensions are ever introduced (and probably not great for performance either).

Re: No CALCULATE Challenge -- Round #2

Greg_Deckler — Sat, 15 Apr 2023 19:56:30 GMT

AlexisOlson Understood but first step is to at least get something that returns the same values under the same conditions.

Also, not to get all nerdy but your calculation can't guarantee that adding additional dimensions will not break the calculation either. For example, if I add a GeographyType dimension and wire it to the Geography table and add the necessary rows for Stores, for example, a store tied to geography code 422 which is a State/Province and a Sales fact row for that Store then your calculation would always include that store row even when I specifically filter out State/Province in a slicer. So it's kind of a bogus requirement that the No CALCULATE version has to be better than the CALCULATE version.

Re: No CALCULATE Challenge -- Round #2

AlexisOlson — Sun, 16 Apr 2023 00:50:49 GMT

Greg_Deckler Got it.

The requirement is that it has to be as good. It's a fair point that there are ways you could modify the model that would cause problems and it's not reasonable to try to protect against any possible change. For the sake of concreteness, let's suppose a user creates a new dimension table Dim_Quantity with a relationship to Sales[SalesQuantity] for the purpose of bucketing sales into Small/Medium/Large orders. This is the sort of thing I need to be robust against.

As it turns out, you don't even need to change the model at all for your suggestion to break down. Try adding a slicer for the existing column Sales[SalesQuantity] to the page and set the filter range to 100-200. This will affect your numerator but not your denominator due to ALL(Sales) and isn't easily remedied by changing this to ALLSELECTED(Sales).

Re: No CALCULATE Challenge -- Round #2

AntrikshSharma — Sun, 16 Apr 2023 08:04:05 GMT

AlexisOlson Great challenge, but because of the requirement I don't think it is possible to make the solution dynamic, so CALCULATE wins, Yay!

Otherwise with the current status of the report I would write this:

Sales Benchmark Ratio 2 = VAR SalesAmount = [Sales Amount] VAR _BenchmarkSales = FILTER ( ALL ( Sales ), RELATED ( Geography[ContinentName] ) = "North America" && RELATED ( Channel[ChannelName] ) = "Store" && RELATED ( 'Product'[ClassName] ) IN VALUES ( 'Product'[ClassName] ) && RELATED ( Promotion[DiscountPercent] ) IN VALUES ( Promotion[DiscountPercent] ) ) VAR Result = DIVIDE ( SalesAmount, SUMX ( _BenchmarkSales, Sales[SalesAmount] ) ) RETURN Result

Re: No CALCULATE Challenge -- Round #2

ValtteriN — Mon, 17 Apr 2023 07:47:13 GMT

Expanding on this logic. I think one solutions would be to use functions such as NATURALINNERJOIN to create a similar table and then use SUMX. This was my first instinct when looking at this (assuming CALCULATETABLE is also forbidden). Otherwise, your solution should be the best.

Re: No CALCULATE Challenge -- Round #2

Greg_Deckler — Tue, 18 Apr 2023 21:50:37 GMT

AlexisOlson This one is as good, cheats as little as possible and is reasonably fast.

Sales Benchmark Ratio NC3 = VAR __GeoKeys = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Geography'), [ContinentName] = "North America"), "GeoKey",[GeographyKey])) VAR __Stores = DISTINCT(SELECTCOLUMNS(FILTER(ALL(Stores), [GeographyKey] IN __GeoKeys),"StoreKey",[StoreKey])) VAR __Channels = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Channel'), [ChannelName] = "Store"),"ChannelKey",[Channel])) VAR __BST = CALCULATETABLE ( 'Sales', REMOVEFILTERS('Geography'), REMOVEFILTERS('Channel') ) VAR __BenchmarkSalesTable = FILTER( FILTER( __BST, [StoreKey] IN __Stores ), [channelKey] IN __Channels ) VAR __BenchmarkSales = SUMX(__BenchmarkSalesTable, [SalesAmount]) VAR __Ratio = DIVIDE ( [Sales Amount], __BenchmarkSales ) RETURN __Ratio

The real issue is that the REMOVEFILTERS function only works within CALCULATE or CALCULATETABLE, which; in theory, I don't exactly see why it couldn't just stand on its own like ALLEXCEPT and return a table or work within a FILTER function. Kind of goes back to that CALCULATE fixation where special functions are engineered to specifically only work with CALCULATE because it's such a powerful function that it needs special functions written spefically for it just so it can do its job...