cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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:

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:

Other friends who might like to join:

1 ACCEPTED SOLUTION
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.

``````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``````
30 REPLIES 30
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!
Super User

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

New Member

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

Super User

Collapsing the snowflake to a star schema is fine.

New Member

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

Super User

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

Super User

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

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!
Super User

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 😎

Super User

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors