Help with CALCULATETABLE

cornwall80 · ‎06-22-2020

Hi,

I've just started to learn DAX and I'm reading a number of different books including the Definitive Guide to DAX and had a question about the CALCULATETABLE vs. CALCULATE functions.

I have tested both these DAX queries and while they produce what looks like the same result set, I was wondering if anyone could tell me if they are in fact syntactically equivalent? Or are there any scenarios when they may produce different results depending on what filters have been applied in the report?

The explanation in the book focuses on CALCULATETABLE version and I found what looks like another version of same query online using CALCULATE.

Verison 1 with Calculate:

BlackTailSpinProds_Calc =
CALCULATE(
    SUM(FactSales[SalesAmount]),
    FILTER(
            ALL(
                DimProduct[BrandName],
                DimProduct[ColorName]
             ),
            AND (
                DimProduct[BrandName]="Tailspin Toys",
                DimProduct[ColorName] = "Black"
            )
    )
)

V2 with CALCULATETABLE:

BlackTailspinProds_CalcTable =
CALCULATE(
    SUM(FactSales[SalesAmount]),
    FILTER(
            CALCULATETABLE(
                            DimProduct ,
                            ALL( DimProduct[BrandName] ),
                            ALL (DimProduct[ColorName])
             ),
            AND (
                DimProduct[BrandName]="Tailspin Toys",
                DimProduct[ColorName] = "Black"
            )
    )
)

Thank you in advance.

regards,

Andrew

cornwall80 · ‎06-22-2020

Hi Harsh,

Thank you for your reply.

I have already watched those videos, but unfortunately I wasn't sure if the two DAX queries I posted were semantically equivalent?

I was wondering if you could see any subtle differences between the two queries which may produce different results e.g. for example different filter contexts.

Kind regards,

Andrew