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 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...
Super UserIt 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.
- Greg_Deckler
AlexisOlson Enough said! LOL!