Help with basic DAX Code

[Anonymous]

Hi to all.

Maybe this is really basic but i've been staring at the code and can't get why this two measures give different results.

I'm using Contoso database to practice:

Measure 1 =
SUMX (
    FILTER (
        SUMMARIZE (
            Sales,
            Sales[Quantity],
            Sales[Net Price]
        ),
        Sales[Quantity] * Sales[Net Price] >= 1000
    ),
    Sales[Quantity] * Sales[Net Price]
)

Measure 2 =

CALCULATE (
    [Total Sales],
    FILTER (
        SUMMARIZE (
            Sales,
            Sales[Quantity],
            Sales[Net Price]
        ),
        Sales[Quantity] * Sales[Net Price] >= 1000
    )
)

Thanks in advance for any help!

[v-xiaoyan-msft]

Hi @Anonymous ,

Glad that your problem has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Greg_Deckler thanks a lot for your reply!

Yes, [Total Sales] is defined as you described.

The problem here is that I see both expressions as equivalent, so I need some more effort understanding evaluations contexts (thought I was doing pretty well and this is quite annoying!)

[AlexisOlson · Accepted solution]

[Total Sales] has to be a measure for it to work inside CALCULATE like this. How is it defined?

If it's defined as SUM ( Sales[Quantity] ) * SUM ( Sales[Net Price] ), then it's nonsensical. If it's defined as SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), then the explanation has to do with granularity.

The FILTER removes the rows with the product under 1000, but Sales and SUMMARIZE ( Sales, Sales[Quantity], Sales[Net Price] ) have different numbers of rows. In Measure 1, you add up the product for each row in the summary table but in Measure 2, you add up each row in Sales, which contains multiple rows with the same Quantity and Net Price instead of just a single row for each distinct combination.

Measure 1 is grouping before summing while Measure 2 only has grouping inside the filter; the actual sum has no grouping by Quantity and Net Price.

Your Measure 2 give the same result as

SUMX (
    FILTER (
        Sales,
        Sales[Quantity] * Sales[Net Price] >= 1000
    ),
    Sales[Quantity] * Sales[Net Price]
)

[Anonymous]

Hi @AlexisOlson thanks a lot for your reply!

Yes,  [Total Sales] is defined as a measure: SUMX(Sales,Sales[Quantity] * Sales[Net Price])

I think I'm seeing the problem now after your explanation.

Please let me know if I interpreted you correctly:

On the Measure 1 I'm not using SUMX over Sales but over a summarized version of sales filtered by quantity * net price >= 1000.

On the Measure 2, I'm using the summarized table to filter Sales inside Calculate and only then applying SUMX on the resulting filtered Sales table. As Sales is not summarized, only filtered, I can have many rows for the same combination of quantity * net price >= 1000.

So, measure 1 has the granularity of the summarized table (less rows) and measure 2 has the granularity of sales (more rows).

Am I right?

Thanks again both of you.

[AlexisOlson]

Nailed it.

[Greg_Deckler]

@Anonymous SUMX is going to evaluate the 2nd parameter for every row in the table and then sum them up. CALCULATE will calculate the value once in the context of the filter expression given assuming Total Sales is Sales[Quantity] * Sales[Net Price].

So which one gives the correct answer?