Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlexisOlson
Super User
Super User

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 without it.

 

In this scenario, I want to define a benchmark ratio as the ratio of sales to sales in North American Stores, preserving all filtering other than geography and channel. Using CALCULATE, this is simple:

 

Sales Benchmark Ratio = 
VAR _BenchmarkSales =
    CALCULATE (
        [Sales Amount],
        ALL ( Geography ),
        Geography[ContinentName] = "North America",
        ALL ( Channel ),
        Channel[ChannelName] = "Store"
    )
VAR _Ratio = DIVIDE ( [Sales Amount], _BenchmarkSales )
RETURN
    _Ratio

 

 The challenge is to write a measure without CALCULATE that can reproduce this robustly.

 

Here's an example of what the values should be with some filters applied:

AlexisOlson_0-1681486417158.png

 

For full credit, the measure should work regardless of what dimensions are used for slicers and what dimensions are used for rows and columns of the matrix. It should still work even if new dimensions are added to the data model.

Participants from last time:

@tamerj1 @AntrikshSharma @Greg_Deckler @Anonymous

 

Other friends who might like to join:

@BA_Pete @bcdobbs @CNENFRNL @smpa01 @parry2k @ValtteriN 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@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.

1.png

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

View solution in original post

30 REPLIES 30
CNENFRNL
Community Champion
Community Champion

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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).

Anonymous
Not applicable

@AlexisOlson - OK, interesting....fair or foul to tinker with the data model and collapse the geography snowflake to simplify?

Collapsing the snowflake to a star schema is fine.

Anonymous
Not applicable

Cool, thanks.  Would you mind throwing the data file into your original post?

@Anonymous I don't have the underlying data files, unfortunately. I downloaded it as a PBIX. 😕

tamerj1
Super User
Super User

@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
Community Champion
Community Champion

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

tamerj1
Super User
Super User

Hi @AlexisOlson 

Doesn't seem that simple 😅

it's 8:15pm right now here and I don't have access to my laptop. I hope not to close the subject until tomorrow same time. I will try to give it a shot anyway from my phone. 
I just wanted to say that even this CALCULATE version of the measure won't work in all scenarios. For example if you have Country as a slicer or if you have any attribute from the Channel table other than Channel Name in the rows, I guess it won't work. So please be nice and grant a little bit of flexibility 😎

Adding a Country slicer doesn't seem to break it as far as I can tell but fair point about the Channel. I didn't use another ALL since there's only one visible column from that table, but I'll go ahead and edit that in.

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.