topic Re: NO CALCULATE theory test1 in DAX Commands and Tips

NO CALCULATE theory test1

Anonymous — Thu, 19 Jan 2023 09:52:00 GMT

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:
Source file Download

Re: NO CALCULATE theory test1

Greg_Deckler — Thu, 19 Jan 2023 02:15:36 GMT

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.

Re: NO CALCULATE theory test1

Anonymous — Thu, 19 Jan 2023 09:41:41 GMT

Hi Greg_Deckler
Please don't give up! 🙂
CALCULATE is not only your own headache...

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:

Re: NO CALCULATE theory test1

Greg_Deckler — Thu, 19 Jan 2023 13:51:27 GMT

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:

1. Start with a table

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.