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

Interesting challenge ðŸ‘‹ Looking forward to some tricky solutions as I've already spent more than half hour but no luck. I really doubt there exists an elegant, error-proof alternative, especially when some new slicers will intervene.

 

Off the track comment on CALCULATE

I admit that the idea to kick the ass of CALCUALTION is buzzy at the first glance; but with further comprehension of DAX, I'd like to say the mechanism of context transition triggered by CALCULATE is a genius one! It differentiates DAX from other traditional "context insensitive" lauguages like Excel formulas, SQL etc.

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

    Yeah, this example demonstrates what I consider the most important use case for CALCULATE -- modifying filter context in a simple and precise way that works in almost any context without needing revision. One can write a measure without CALCULATE that works for a specific visual but if it can't reliably be reused in other contexts, we're back to the bad old days of SSRS where we often need separate queries for each visual.

     

    Much of my day-to-day work is building and maintaining common data models that feed dozens of thin reports  (I don't know in advance every possible context that each measure will be used) and this type of situation (where just one or two filters need to be removed or adjusted) comes up frequently with benchmarks and 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).