Understanding context transition - duplicate rows

[gazpage]

So I thought I was starting to understand context transition, but I got thrown a curveball over the weekend.

I have a dataset that records sales, where each row is a single sale transaction. Columns include a customerid and product id that both connect out to dimension tables, but also includes various other items, such as number of units, sale price, unit cost etc.

From this I have a measure

[Total Profit]:= SUM ( [Sales Price] ) + SUM ( [Unit Cost] ) + etc

basically adding up all the columns to give a profit. This has worked fine for me in general.

I've then created a measure that uses SWITCH( ), that iterates down the rows, and depending on the product type multiplies [Total Profit] by a rebate factor. This measure is where I got some surprising results, but you can see the same thing in this simple version of the measure (which you would never want to use in practice).

SUMX (
    Sales,
    [Total Profit]
)/[CODE]

I thought this would work as using the Total Profit measure would carry out context transition and Total Profit would be calculated for each row. Actually what happens is I am seeing double counting of duplicate rows, ie sales in the same month of the same product to the same customer.

Experimenting, I find that what is happening is that the context transition is not converting the particular row to a filter for the SUM, it's converting it to a filter of all rows where every column matches the current row. A solution is to actually write out Total Profit with just the underlying columns, rather than a measure with SUMs, but I am now wondering if a bunch of other formulas I have written suffer from this same issue wherever I might have duplicated rows.

Am I understanding what is happening here correctly? Can anyone point me to any blogs or documentation on this point so I can understand this properly.

Thanks
Gary

 

[akice]

Here are articles by the best:

https://www.sqlbi.com/articles/understanding-context-transition/
https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/
https://www.sqlbi.com/articles/context-transition-and-expanded-tables/

context transition is not converting the particular row to a filter for the SUM, it's converting it to a filter of all rows where every column matches the current row.

This is correct. Context transition is when a row context is transitioned into an equivalent filter context. Since your SUMX is iterating over entire table, all columns are transitioned and will filter SALES table - including columns like 'Price' or 'Color' or 'Address' or 'Unit Price' or 'Quantity' or 'Unit of Measure'. Again, all columns. Once new filter context is constructed, it is applied to the model and value is computed. SUMX ( SALES, ...) should already be restricted to rows visible in the current filter context prior to context transition.

Usually it is best to limit values to iterate over like: SUMX ( VALUES ( Sales[CustomerID] ), ... ) or SUMX ( SUMMARIZE ( Sales, Sales[CustomerID], Sales[ProductID]), ...) But totally depends on desired output (which you don't indicate what you have on rows/columns).

And finally, with respect to:

[Total Profit]:= SUM ( [Sales Price] ) + SUM ( [Unit Cost] ) + etc

For readability and consistency, the best practice is to preface column names with the table name like: 'Sales[Sales Price]', but do not preface measures with table name: '[Total Profit]'. Hope this helps...