No CALCULATE Challenge -- Round #2

[AlexisOlson]

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 

[tamerj1 · Accepted solution]

@AlexisOlson and All,

I was completely convinced that No CALCULATE solution wasn't possible for such kind of problems. My belief is that filter context manipulation is exclusively CALCULATE branded. Yesterday while handling a DAX issue, @AlexisOlson No CALCULATE challenge popped up in my mind. With a probable solution, I decided to try the first thing in the morning and seems it covers all the requirements.

Sales Benchmark Ratio 2 = 
VAR _BenchmarkSales =
    SUMX (
        ALLSELECTED ( Geography[RegionCountryName] ),
        SUMX ( 
            CROSSJOIN (
                TREATAS ( { "North America" }, Geography[ContinentName] ),
                TREATAS ( { "Store" }, Channel[ChannelName] )
            ),
        [Sales Amount]
        )
    )
VAR _Ratio =
    DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
    _Ratio

[Greg_Deckler]

@tamerj1 Brilliant! Nice one Tamer. Going to add that one to my pattern of No CALCULATE tricks.

[tamerj1]

@Greg_Deckler 

Shall we expect a new one of your brilliant videos? Tag me if you do 😉

[AlexisOlson]

@tamerj1 Nice work! This does appear to satisfy the requirements, so I'll accept it. The trick here is using the implicit CALCULATE context transition that comes from referencing another measure, [Sales Amount].

For an extra challenge, can anyone modify this so that it works as a standalone measure? Maybe using SUMMARIZE since that can also do a context transition.

[tamerj1]

@AlexisOlson 
Yes sure, I thought about that already but it does not look so nice

Sales Benchmark Ratio 2 = 
VAR _BenchmarkSales =
    SUMX (
        SUMMARIZE ( 
            CROSSJOIN ( 
                ALLSELECTED ( Geography[RegionCountryName] ),
                CROSSJOIN (
                    TREATAS ( { "North America" }, Geography[ContinentName] ),
                    TREATAS ( { "Store" }, Channel[ChannelName] )
                )
            ),
            Geography[RegionCountryName],
            Geography[ContinentName],
            Channel[ChannelName],
            "@Amt", SUM ( Sales[SalesAmount] )
        ),
    [@Amt]
    )
VAR _Ratio =
    DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
    _Ratio

[AntrikshSharma]

@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

[Greg_Deckler]

@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

[AlexisOlson]

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

[Greg_Deckler]

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

[AlexisOlson]

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

[Greg_Deckler]

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

[AlexisOlson]

It cheats as little as possible but is clearly still a cheat at the core using CALCULATETABLE. Even with the cheat, it's more cumbersome, less readable, less maintainable, and still doesn't meet the new dimension criterion.

I believe the "real issue" is that there are highly useful functions like CALCULATE and CALCULATETABLE that you choose to do without for your own personal reasons. There's no good reason for Microsoft to create additional syntax variations to suit your idiosyncratic choice to artificially limited your choice of functions when there are existing options that work perfectly fine and your limitations are self-imposed.

Unless someone else comes along with a fundamentally different solution, it seems to me that this challenge demonstrates that CALCULATE/CALCULATETABLE are key functions for adjusting filter context, and workarounds to avoid them have serious drawbacks.

[Greg_Deckler]

@AlexisOlson It absolutely meets the new dimension criteria as much as the original solution does. If any new dimension table is added, those dimension filters will be respected since the formula only removes filters from Geography and Channel tables and then adds back in the appropriate geography and channel filters. That's as good as the original where, as I explained before, the original calculation cannot guarantee changes in the data model affecting those dimensions. 

I've never said CALCULATE didn't have it's uses. What I have always said, is that it has very limited real world uses. Given all of the stars in the universe aligning and given relatively recent special functions written specifically for it like USERELATIONSHP, REMOVEFILTERS, etc. it has some utility. However, that utility is overshadowed by just how much pain and suffering CALCULATE has caused new users and business users to DAX. DAX culture is broken. Plain and simple. It's a CALCULATE snobbery that excludes new users of DAX and businesss users who are coming from Excel and need a less steep learning curve that doesn't include immediately having to learn star schemas and the complexities of CALCULATE. 

[AlexisOlson]

Sorry, you are correct regarding the dimension criterion. I misinterpreted the first few variables. The CALCULATETABLE usage you have is indeed analogous to the CALCULATE I used in the original.

Apologies for that mistake and any general crankiness. I was rather impatient and frustrated from having just gotten off the phone with Microsoft support.

[v-rzhou-msft]

Hi @AlexisOlson ,

Could you tell me if your issue has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your issue or share me with your pbix file without sensitive data.

Best Regards,

Rico Zhou

[AlexisOlson]

There is no qualifying solution so far, so I can't mark it as solved. The calculation can obviously be reproduced without CALCULATE/CALCULATETABLE but no such solution has been robust in the way I specified as the critical aspect of the challenge.

I attached a .pbix in the original post along with details.

[Greg_Deckler]

@AlexisOlson Enough said! LOL!

[tamerj1]

@Greg_Deckler 

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

[Greg_Deckler]

@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

[ValtteriN]

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.