Differences in using measure and formula in the measure

[Youngmyung]

When making SUMX Measure ("Sales", "Sales Quantity"), the measure of SUMX ("Unit Price", SUMX ('Sales', [Sales Quantity]) and the measure of =SUMX ('Unit Price', [SUMX Measure]) are different. What is the difference between the measure and the formula?

In addition, SUMX ('Unit Price', SUMX ('Sales', Sales' [Sales Quantity]) and =SUMX ('Sales', [SUMX Measure]) show the number of rows in the Unit table and Sales table multiplied by [SUMX Measure] respectively, which is difficult to understand. Please explain.

[TomMartens · Accepted solution]

Hey @Youngmyung ,

read this article carefully: https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html
Jeffrey Wang is considered being one of the masterminds behind DAX, one of the many revelations of the article is what happens when there is a calculate:

Calculate function performs the following operations:

1.        Create a new filter context by cloning the existing one.
2.        Move current rows in the row context to the new filter context one by one and apply blocking semantics against all previous tables.
3.        Evaluate each setfilter argument in the old filter context and then add setfilter tables to the new filter context one by one and apply blocking semantics against all tables that exist in the new filter context before the first setfilter table is added.
4.        Evaluate the first argument in the newly constructed filter context.

Please be aware that referencing a measure inside a DAX statements implicitely wraps a CALCULATE around it, performing the 4 steps of the CALCULATE.

A table iterator like SUMX creates a row context for the numeric expression, when referencing a measure in the <numeric expression> part of SUMX, the 4 steps are happening.

Next to this "general" explanation, I have my difficulties to provide more specific advise, explanation, because your question contains flaws:

• this is not valid DAX code: SUMX Measure ("Sales", "Sales Quantity")
  
• maybe a closing bracket is missing here: SUMX ("Unit Price", SUMX ('Sales', [Sales Quantity])
  
• not sure if [Sales Quantity] is a column reference, or a measure reference. I'm talking about this piece of DAX: SUMX ("Unit Price", SUMX ('Sales', [Sales Quantity])
  Be aware that it's considered to use the tablename like so 'Sales'[Sales Quantity] when referencing columns and omit the tablename when referencing a measure.
  
• [SUMX Measure] is not defined

Nested iterations like SUMX ("Unit Price", SUMX ('Sales', [Sales Quantity]) are difficult especially when there is no relationship between the two tables, without knowing the data model, it's difficult to explain what you consider being a multiplication. When there is a relationship, this might return a different result:
SUMX ("Unit Price", CALCULATE(SUMX ('Sales', [Sales Quantity])))
Be aware, that the snippet contains brackets that might be wrong.

Nevertheless, hopefully, this helps to tackle your challenge.

Regards,

Tom