Forum Discussion
How to retrieve value between two tables based on multiple slicer values
I am trying to work on a scenario where I have two tables and I need to look up in table2 based on multiple slicer values coming from one table. Below are the details and your inputs are highly appreciated -
The slicers will be on CompanyCode, To Currency, Rate type, date.
CompanyCode can be multiple selected values, and each company code will have one asscociated FromCurrency value and rest all slicers will be single select values. I don’t have a relationship between these two tables. I need to look up for a exchange rate on table2 and multiply revenue on table 1 with the exchange rate retrieved from table 2 based on slicer values.
Basically how it works is for example - if two company codes are selected in slicer, then we need to look up on table 2 using the two FromCurrency values that are filtered down on table 1 as each companycode will be associated with one FromCurrencyValue.
Right now I am able to achieve it if only one companycode is selected using below measure where I am filtering the table down to each selected value in slicer. Selected values are calculated as different measures and provided as input in below query exchangeRateCalculate -
SelectedRateType = SELECTEDVALUE(TABLE2[rate_type])
exchangeRateCalculate = CALCULATE(VALUES(TABLE2[exch_rate]),CALCULATETABLE(TABLE2,FILTER(TABLE2,TABLE2[fromcurrency] = [SelectedFromCurrency]) ,TABLE2[tocurrency] in VALUES(TABLE2[tocurrency]),TABLE2[rate_type] in VALUES(TABLE2[rate_type]),FILTER(TABLE2,TABLE2[rateeffectivefromdate] <= [SelectedDate] && TABLE2[rateeffectivetodate] >= [SelectedDate])
v-lili6-msft thanks for looking into it. My file is so large to share and I got what I wanted anyway. I was able to select multiple and filter by companycodes using AllSelected function modfiying my exisitng DAX
exchangeRateCalculate =
var Exch = CALCULATE(Sum(TABLE2[exch_rate]), table1(companycode in allselected (table1[Companycode]),
TABLE2[fromcurrency] in values (table1[fromcurrency] ,TABLE2[tocurrency] in VALUES(TABLE2[tocurrency]),TABLE2[rate_type] in VALUES(TABLE2[rate_type]),FILTER(TABLE2,TABLE2[rateeffectivefromdate] <= [SelectedDate] && TABLE2[rateeffectivetodate] >= [SelectedDate])
return exch
2 Replies
hi, Anonymous
Could you please share us your sample pbix file or some sample data and the expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
v-lili6-msft thanks for looking into it. My file is so large to share and I got what I wanted anyway. I was able to select multiple and filter by companycodes using AllSelected function modfiying my exisitng DAX
exchangeRateCalculate =
var Exch = CALCULATE(Sum(TABLE2[exch_rate]), table1(companycode in allselected (table1[Companycode]),
TABLE2[fromcurrency] in values (table1[fromcurrency] ,TABLE2[tocurrency] in VALUES(TABLE2[tocurrency]),TABLE2[rate_type] in VALUES(TABLE2[rate_type]),FILTER(TABLE2,TABLE2[rateeffectivefromdate] <= [SelectedDate] && TABLE2[rateeffectivetodate] >= [SelectedDate])
return exch
