cancel
Showing results 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

Anonymous
Not applicable

NO CALCULATE theory test1

Curious about @Greg_Deckler  NO CALCULATE theory (video, article)
Looks very atrractive, but needs to test.

For example:
I want to rank Date based on all subgroups mentioned in matrix Rows section except Date.
Examples

So, it means that I want to avoid explicit use of Type and subType columnnames in measures/calculations

My DAX measures (NB! without IF statements these measures makes cartesian product for subType (details)):

``````lastDate2 =
IF(
NOT ISEMPTY(tstTable),
CALCULATE(
LASTDATE(tstTable[Date]),
REMOVEFILTERS(tstTable[Date])
)
)``````
``````rnkDate2 =
VAR t =
CALCULATETABLE(
VALUES(tstTable[Date]),
REMOVEFILTERS(tstTable[Date])
)
RETURN
IF(
NOT ISEMPTY(tstTable),
RANKX(
t,
LASTDATE(tstTable[Date])
)    ``````

it explicitly uses CALCULATE and CALCULATETABLE
QUESTION: how to get the same functionality without explicit use of these functions?

Data Model:

1 ACCEPTED SOLUTION
Super User

@Anonymous Well, variable use is recommended whether using CALCULATE or not using CALCULATE. The difference in the No CALCULATE approach is that it generally eschews the use of CALCULATE for more "readable" and less "complex" DAX. CALCULATE is a super complex function that can easily give really kind of bizarre and wacky results, particularly in single table data models. It is very "black box" meaning you can't debug internally what exactly it is doing. But, the reality is that CALCULATE is really just a filter function. Essentially you can state what CALCULATE does with the words:

"Go perform this calculation but modify the filter context with these filter clauses I am specifying"

Now, how it goes about that is black box and complex. So, instead, the idea of the No CALCULATE approach is simple:

2. Filter and/or group the table as appropriate

3. Use an X aggregator to aggregate across your filtered/grouped table

By using this simple, 3 step approach, most DAX problems can be solved quite easily and by doing this process using variables it is super easy to debug and understand every step of what is going on. And CALCULATE is not necessary (because it's really just a filtering function after all and you can do most of that using FILTER).

There are complex scenarios where you have to use CALCULATE but this is more because Microsoft has designed filter functions that only work with CALCULATE rather than the No CALCULATE process itself. So, there will always be scenarios where you *have* to use CALCULATE but they are minimal and would likely be completely eliminated if Microsoft actually made functions like REMOVEFILTERS and other similar functions work outside of CALCULATE or provide equivalent functions.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Anonymous
Not applicable

Hi @Greg_Deckler

Probably, It would be too ideal to exclude CALCULATE usage everywhere.
But, If it's not possible, it would be nice to have a list of  "NO CALCULATE" limitations.

Screens from @Greg_Deckler ALLEXCEPT without CALCULATE video:
Before:

After:

For me this topic looks corelated with SQLBI experts materials about Variables in DAX.
Basicaly, you are talking the same things by different words: how to write code that works like estimated.
Just "NO CALCULATE" title is a bit more "flashy".

Last example by  @marcorusso   Debugging DAX measures in Power BI (first 12 minutes):
Before: Beautiful&Compact, but not working as estimated:

After: Verbose, but understandable&debuggable and working as estimated:

Super User

@Anonymous Well, variable use is recommended whether using CALCULATE or not using CALCULATE. The difference in the No CALCULATE approach is that it generally eschews the use of CALCULATE for more "readable" and less "complex" DAX. CALCULATE is a super complex function that can easily give really kind of bizarre and wacky results, particularly in single table data models. It is very "black box" meaning you can't debug internally what exactly it is doing. But, the reality is that CALCULATE is really just a filter function. Essentially you can state what CALCULATE does with the words:

"Go perform this calculation but modify the filter context with these filter clauses I am specifying"

Now, how it goes about that is black box and complex. So, instead, the idea of the No CALCULATE approach is simple:

2. Filter and/or group the table as appropriate

3. Use an X aggregator to aggregate across your filtered/grouped table

By using this simple, 3 step approach, most DAX problems can be solved quite easily and by doing this process using variables it is super easy to debug and understand every step of what is going on. And CALCULATE is not necessary (because it's really just a filtering function after all and you can do most of that using FILTER).

There are complex scenarios where you have to use CALCULATE but this is more because Microsoft has designed filter functions that only work with CALCULATE rather than the No CALCULATE process itself. So, there will always be scenarios where you *have* to use CALCULATE but they are minimal and would likely be completely eliminated if Microsoft actually made functions like REMOVEFILTERS and other similar functions work outside of CALCULATE or provide equivalent functions.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

@Anonymous Not sure if you can avoid explicitly referencing Type and Subtype in that case. It's an unforunate aspect of CALCULATE mania that Microsoft has created certain functions like REMOVEFILTERS that only work as a filter clause within the CALCULATE function versus just a general filter function like ALL and ALLEXCEPT.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors