Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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
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
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.
@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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |