Forum Discussion

alsm's avatar
alsm
Icon for Helper III rankHelper III
3 years ago
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 ...
  • alsm's avatar
    alsm
    3 years ago

    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
v-yinliw-msft's avatar
v-yinliw-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi alsm ,

 

I think you can add a column or a measure in the FX table to calculate the LimitVolume * Rate.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • alsm's avatar
    alsm
    Icon for Helper III rankHelper III
    3 years ago

    v-yinliw-msft  Thank you for your reply...

    I need to filter first before i do this multiply

     

    so for 28Feb, the filters are

    Header Table
    HeaderKeyDetailsKeyValidFromValidTo
    H1D11Jan202231Dec2022
    H2D215Feb2022

    31Oct2022

     

    Details Table
    DetailsKeyNameCurrencyVolume
    D1AEUR500
    D2BGBP700

     

    Limit Table   
    HeaderKeyLimitVolumeValidFromValidTo
    H145015Feb202231Mar2022
    H270015Feb20221Mar2022

     

    FX Table  
    DateCurrencyRate
    28-FebEUR1.034
    28-FebGBP1.6

     

    and this filtered table need to be multiplied ...

    problem is i cannot get my head around on how to apply complex filter (validfrom and validto) and use the result to multiply