ADDCOLUMNS and the propogation of row context to SUMX

[londondaxcoder]

Substantially simplifying my problem description as evidently people feel it is too much to read and take in. Any help would be appreciated.

The part of my code which is producing incorrect results is the calculation of "Book Return By Date".

If ADDCOLUMNS is iterating BookByDate, I assumed that a call to SUMX (enclosed in CALCULATE) which is also iterating BookByDate would only be able to see the row in BookByDate that is currently being iterated by ADDCOLUMNS (due to context transition) however SUMX seems to be able to see all rows in BookByDate. Can anyone tell me why this is and how I can resolve the problem?

I am aware that I could write this code in a different way, but I have substantially simplified this vs my real code, and am trying to understand the reason for this problem because I regularly need to perform intermediate calculations in my DAX code.

DEFINE
VAR mandateKey1 = 
CALCULATETABLE
(
	VALUES(Mandate[mandateKey]),
	Mandate[Mandate Name] = "Mandate 1"
)

VAR portfolioKey1 = 
CALCULATETABLE
(
	VALUES(Portfolio[portfolioKey]),
	Portfolio[Portfolio Name] = "Portfolio 1"
)

VAR bookKey1 = 
CALCULATETABLE
(
	VALUES(Book[bookKey]),
	Book[Book Name] = "Book 1"
)

VAR classKey1 = 
CALCULATETABLE
(
	VALUES('Class'[classKey]),
	Class[Class Name] = "Class 1"
)

VAR PnLFiltered =
CALCULATETABLE
(
	PnL,
	mandateKey1,
	portfolioKey1,
	bookKey1,
	classKey1
)

VAR BookByDate =
ADDCOLUMNS
(
	SUMMARIZE
	(
		PnLFiltered,
		Mandate[mandateKey],
		Portfolio[portfolioKey],
		'Date'[dateKey],
		Class[classKey],
		Book[bookKey]
	),		
	"@ABS SUM SOD", //Gives correct result
	CALCULATE
	(
		IF
		(
	        SUM(PnL[SOD]) = 0,
	        BLANK(),
	        ABS(SUM(PnL[SOD]))
	    )
	 )
)

VAR BookReturnsByDate = 
ADDCOLUMNS
(
	BookByDate,
	"Book Return By Date", //Wrong result because all rows of BookByDate are being iterated by SUMX
	CALCULATE
	(
		IF(
			SUM(PnL[PnL Total]) <= (-1 * SUMX(BookByDate, [@ABS SUM SOD)),
			0,
			IF(
		        ISBLANK(DIVIDE(SUM(PnL[PnL Total]), SUMX(BookByDate, [@ABS SUM SOD))), 
		        0, 
		        DIVIDE(SUM(PnL[PnL Total]), SUMX(BookByDate, [@ABS SUM SOD))
		      )
		  )
	),	
	//Debugging/troubleshooting:
	"SUM(PnL[PnL Total])", //Gives correct result
	CALCULATE(SUM(PnL[PnL Total])),
	"CALCULATE(SUMX(BookByDate, [@ABS SUM SOD))", //Gives incorrect result because all rows of BookByDate are being iterated by SUMX
	CALCULATE(SUMX(BookByDate, [@ABS SUM SOD)),
	"DumpFilters All Tables",
	[DumpFilters All Tables], //A measure generated from DAX Studio showing all filters applied to all tables - shows just a single date in the filter context
	"CONCATENATEX SUMX(BookByDate, [@ABS SUM SOD)",
	CALCULATE(CONCATENATEX(BookByDate,'Date'[dateKey])) //Indicates that the other iterator function I am using (SUMX) is iterating all rows in BookByDate not just the row of BookByDate currently being iterated by ADDCOLUMNS
)

EVALUATE 
BookReturnsByDate
ORDER BY 'Date'[dateKey]

[lbendlin]

Welcome to the "Fighting the API"  nightmare.  Have you considered using the existing Aggregations function in Power BI instead? Or is that not possible due to the extra logic applied for your temporary aggregation tables?

[londondaxcoder]

Hi @lbendlin thanks for your response.

Unfortunately we can't use the built in Power BI aggregations feature because the calculations we are doing are not straight forward sum, addition, subtraction etc. We normally have to group the data at a certain level and then perform the calculation at that level (eg: we can't calculate daily returns on an investment, and then sum up those daily returns to find the monthly return, we need to instead group the data at the month granularity and then sum returns since the previous month). Those calculations use less standard mathematical functions like EXP, LOG etc.

In general we need to be able to perform relatively complex intermediate calculations in DAX. The equivalent would be where you have a T-SQL stored procedure with 3 or 4 temp tables/table variables, each one using the previous one for it's work and doing intermediate calculations in order to get to the final result.

Although I've given plenty of explanation in my post for what I'm trying to do, to avoid getting off topic, the key thing I'm trying to establish in this question is the following: If ADDCOLUMNS is iterating BookByDate, I assumed that a call to SUMX which is also iterating BookByDate would only be able to see the row in BookByDate that is currently being iterated by ADDCOLUMNS (due to context transition) however SUMX seems to be able to see all rows in BookByDate.

Thanks again for your help.

[lbendlin]

ok I'll have another look at the Definitive Guide to DAX to see if they mention any context transition issues for these operations. As you mentioned there needs to be a way to break the logical connections between the intermediate steps.