Forum Discussion
alsm
Helper III
Helper IIIFilter before applying sumx
I am performing sumx with multiplication across two tables as follows ResultUSD2 = VAR ResUSD = SUMX(ResultsLCYTbl, ResultsLCYTbl[ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxT...
I figured it out!!
ResultUSD2 =VAR select_date = MAX(EomDates[Date])VAR filt_headers = SUMMARIZE(FILTER(HeaderTbl, [ValidFrom] <= select_date && [ValidTo] >= select_date), HeaderTbl[HeaderKey])VAR filt_resultslcytbl = FILTER(ResultsLCYTbl, ResultsLCYTbl[HeaderKey] in filt_headers)VAR ResUSD = SUMX(filt_resultslcytbl, [ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))ReturnResUSDfor me what was a revelation was in line
VAR ResUSD = SUMX(filt_resultslcytbl, [ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))using [ResultInLCY] directly in second part of SUMX, earlier i was trying to use it as
filt_resultslcytbl[ResultInLCY], which power bi complained
alsmHelper III
Helper IIII figured it out!!
ResultUSD2 =
VAR select_date = MAX(EomDates[Date])
VAR filt_headers = SUMMARIZE(FILTER(HeaderTbl, [ValidFrom] <= select_date && [ValidTo] >= select_date), HeaderTbl[HeaderKey])
VAR filt_resultslcytbl = FILTER(ResultsLCYTbl, ResultsLCYTbl[HeaderKey] in filt_headers)
VAR ResUSD = SUMX(filt_resultslcytbl, [ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))
Return
ResUSD
for me what was a revelation was in line
VAR ResUSD = SUMX(filt_resultslcytbl, [ResultInLCY]*CALCULATE(VALUES(FxTbl[Rate]), TREATAS(SUMMARIZE(FxTbl,FxTbl[Currency],FxTbl[Date]),ResultsLCYTbl[Currency],ResultsLCYTbl[Date])))
using [ResultInLCY] directly in second part of SUMX, earlier i was trying to use it as
filt_resultslcytbl[ResultInLCY], which power bi complained