Forum Discussion
Solved
Lookup value on filtered table
hello, I have multiple tables that need to be filtered on selected date and data from different tables multiplied. Any hints? Header Table HeaderKey DetailsKey ValidFrom ValidTo H1 D1 ...
I solved it by adding one column after another using ADDCOLUMNS. Happy to hear from someone a more DAX like solution (this was closer to series of Lookups)
VolumeLimit (USD) = VAR FilteredHeaderTbl = FILTER(ALL(HeaderTbl), HeaderTbl[ValidFrom] < [SelectEoM] && HeaderTbl[ValidTo] >=[SelectEoM]) VAR FilteredNames = CALCULATETABLE(VALUES(HeaderTbl[DetailsKey]),FilteredHeaderTbl) VAR FilteredHeaderIds = CALCULATETABLE(VALUES(HeaderTbl[HeaderKey]),FilteredHeaderTbl) VAR FilteredLimitTbl = CALCULATETABLE(VolumeLimitTbl,FILTER(VolumeLimitTbl, VolumeLimitTbl[ValidFrom] < [SelectEoM] && VolumeLimitTbl[ValidTo] >= [SelectEoM]), FilteredHeaderIds) VAR FilteredDetailsTbl = CALCULATETABLE(DetailsTbl,FilteredNames) VAR WithHeaderId = ADDCOLUMNS(FilteredDetailsTbl,"hkey",CALCULATE(MAX(HeaderTbl[HeaderKey]),FILTER(FilteredHeaderTbl, [DetailsKey] == EARLIER(DetailsTbl[DetailsKey])))) VAR WithLimit = ADDCOLUMNS(WithHeaderId,"vol_limit", CALCULATE(MAX(VolumeLimitTbl[LimitVolume]),FILTER(FilteredLimitTbl,[HeaderKey] == EARLIER([hkey])))) VAR WithFX = ADDCOLUMNS(WithLimit,"rate", LOOKUPVALUE(FxTbl[Rate],FxTbl[Date],[SelectEoM],FxTbl[Currency],[Currency])) VAR TotalLimit = CALCULATE(SUMX(WithFX,[vol_limit]*[rate])) RETURN TotalLimit
I solved it by adding one column after another using ADDCOLUMNS. Happy to hear from someone a more DAX like solution (this was closer to series of Lookups)
VolumeLimit (USD) =
VAR FilteredHeaderTbl = FILTER(ALL(HeaderTbl), HeaderTbl[ValidFrom] < [SelectEoM] && HeaderTbl[ValidTo] >=[SelectEoM])
VAR FilteredNames = CALCULATETABLE(VALUES(HeaderTbl[DetailsKey]),FilteredHeaderTbl)
VAR FilteredHeaderIds = CALCULATETABLE(VALUES(HeaderTbl[HeaderKey]),FilteredHeaderTbl)
VAR FilteredLimitTbl = CALCULATETABLE(VolumeLimitTbl,FILTER(VolumeLimitTbl, VolumeLimitTbl[ValidFrom] < [SelectEoM] && VolumeLimitTbl[ValidTo] >= [SelectEoM]), FilteredHeaderIds)
VAR FilteredDetailsTbl = CALCULATETABLE(DetailsTbl,FilteredNames)
VAR WithHeaderId = ADDCOLUMNS(FilteredDetailsTbl,"hkey",CALCULATE(MAX(HeaderTbl[HeaderKey]),FILTER(FilteredHeaderTbl, [DetailsKey] == EARLIER(DetailsTbl[DetailsKey]))))
VAR WithLimit = ADDCOLUMNS(WithHeaderId,"vol_limit", CALCULATE(MAX(VolumeLimitTbl[LimitVolume]),FILTER(FilteredLimitTbl,[HeaderKey] == EARLIER([hkey]))))
VAR WithFX = ADDCOLUMNS(WithLimit,"rate", LOOKUPVALUE(FxTbl[Rate],FxTbl[Date],[SelectEoM],FxTbl[Currency],[Currency]))
VAR TotalLimit = CALCULATE(SUMX(WithFX,[vol_limit]*[rate]))
RETURN
TotalLimit