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

AlexisOlson 

Not the best performance for sure 

Sales Benchmark Ratio =
VAR _BenchmarkSales =
SUMX (
FILTER (
ALLSELECTED ( Sales ),
RELATED ( Geography[ContinentName] ) = "North America"
&& RELATED ( Channel[ChannelName] ) = "Store"
),
Sales[Quantity] * Sales[Price]
)
VAR _Ratio =
DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
_Ratio

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
3 years ago

Inspiring way addressing this tricky chanllenge; but I'm afraid

FILTER(
    ALLSELECTED( Sales ),
    RELATED( Geography[ContinentName] ) = "North America"
        && RELATED( Channel[ChannelName] ) = "Store"
)

would produce an empty table because there exist an external slicers Channel[ChannelName] and Geography[ContinentName]; when Channel[ChannelName]<>"Store" or Geography[ContinentName]<>"North America" are selected, the filtered table is empty.