Forum Discussion
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 | 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?
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
3 Replies
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 TotalLimitHi 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.
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