Using CALCULATE or not

[vgeldbr]

I'm looking for some experienced guidnce on whether best practice is to use CALCULATE or not. See these two measures which both produce the correct result. Which is considered the right or best way to create this measure?

OPTION 1:

CALCULATE(
    SUMX(
        'ITRDB Daily_ProjectFinancial',
        'ITRDB Daily_ProjectFinancial'[MTD]
    ),
    'ITRDB Daily_ProjectFinancial'[Dimension] = "Plan",
    NOT CONTAINSSTRING(
        'ITRDB Daily_ProjectFinancial'[Budgetcomponent],
        "Total"
    )
)

OPTION 2:

SUMX(
    FILTER(
        'ITRDB Daily_ProjectFinancial',
        'ITRDB Daily_ProjectFinancial'[Dimension] = "Plan"
            && NOT CONTAINSSTRING(
                'ITRDB Daily_ProjectFinancial'[Budgetcomponent],
                "Total"
            )
    ),
    'ITRDB Daily_ProjectFinancial'[MTD]
)

[Whitewater100]

Hi:

Option 2 is iterating two times(SUMX & FILTER) where the first option can use compression efficiencies built into the vertipaq engine. When I read about this there is a lot of it dpends. How large your model is, the degree of cardinality, etc,etc.

You can do your own test with two different visuals using these options and run the performance analyzer that times your calculations. I'll paste a shot of that here.

From there in DAX Studio you can study DMV's and Server timings. Generally, I understand having the SE(storae engine) carrying the workload is better. FE formula engine is the other piece of the engine.

I have seen it proven that CalculateTable is faster then Filter but again lots of factors playing into this behind the scenes. SQLBI website has much information on this topic.

To answer your question you can get a feel for what amount of time it takes to run either query, for your model.

I hope this helps.

[vgeldbr]

@Whitewater100  thanks for your thoughts on performance differences. I'd not considered that aspect and will look into it as you suggest. Other than performance differences, are there any other considerations as to which approach is considered the better practice?

[Whitewater100 · Accepted solution]

Hi:

Nested Iterators can be tricky and that's another reason voting for option number 1. Here is an article on the topic. The iterators have to go thru your table row by row and SUMX and FILTER are iterators.

I hope this article will fill in any blanks. As a rule of thumb is if it is processed faster with less burden on the vertipaq, it is better. Saying too much more would be just guessing as the factors I mentioned earlier al come into play. Avoiding nested iterators, when possible, is better and will result in less double counting that comes with nested iterators if you are not careful.

Article:

https://www.sqlbi.com/tv/optimizing-nested-iterators-in-dax/

I hope this solves your question.  Lot's of data out there on this but I have given you he highlights. Thanks..