Lookup value on filtered table

[alsm]

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	1Jan2022	31Dec2022
H2	D2	15Feb2022	31Oct2022
H3	D3	10Mar2022	15Sep2022

Details Table
DetailsKey	Name	Currency	Volume
D1	A	EUR	500
D2	B	GBP	700
D3	C	AUD	250

Limit Table
HeaderKey	LimitVolume	ValidFrom	ValidTo
H1	500	1Jan2022	15Feb2022
H1	450	15Feb2022	31Mar2022
H1	400	1Apr2022	31Dec2022
H2	700	15Feb2022	1Mar2022
H2	550	1Mar2022	31Oct2022
H3	250	10Mar2022	1May2022
H3	250	1May2022	15Sep2022

FX Table
Date	Currency	Rate
31-Jan	CAD	1.2
31-Jan	EUR	1.1
31-Jan	GBP	1.07
31-Jan	AUD	1.4
31-Jan	USD	0.9
28-Feb	CAD	1.3
28-Feb	EUR	1.034
28-Feb	GBP	1.6
28-Feb	AUD	1.3
28-Feb	USD	1

If SelectedValue for date = 28Feb, I want to show on screen

Currency	LimitVolume	LimitVolume (USD)
EUR	450	465.3
GBP	700	1120

What I have to do (if I were to write a program)

1. I have filtered HeaderTable first find what is valid on 28Feb

2. I have found all the distinct currency from Details for the header

3. I have filtered LimitTable to find LimitVolume on 28Feb

4. I have found FX rate on 28-Feb for the currencies

5. Grouped by currency shown Limit in Local currency & the convered USD value

No combination of lookup on filtered table to do LimitVolume*Rate seems to be working

Any other way that Lookup?

[alsm · Accepted solution]

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]

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]

@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
HeaderKey	DetailsKey	ValidFrom	ValidTo
H1	D1	1Jan2022	31Dec2022
H2	D2	15Feb2022	31Oct2022

Details Table
DetailsKey	Name	Currency	Volume
D1	A	EUR	500
D2	B	GBP	700

Limit Table
HeaderKey	LimitVolume	ValidFrom	ValidTo
H1	450	15Feb2022	31Mar2022
H2	700	15Feb2022	1Mar2022

FX Table
Date	Currency	Rate
28-Feb	EUR	1.034
28-Feb	GBP	1.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