Forum Discussion

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago
Solved

No CALCULATE Challenge -- Round #2

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 witho...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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