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 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- tamerj1
"It should still work even if new dimensions are added to the data model." said AlexisOlson
- 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).
