Forum Discussion
Super UserNo CALCULATE Challenge -- Round #2
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
Community ChampionAlexisOlson 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.
Super UserGreg_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 __RatioThe 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.