Forum Discussion
how to implement subquery in power bi
Hi fmariesh ,
Based on the understanding of your requirement, I have created the Fact and Dimension tables and connected them based in Currency table.
Also created a new date table based on standard M query and also related them based on which you will be able to filter out ypur records.
For the multiplication I created a new calculated column using the below DAX:
column = FactContractDetails[Txn Amount] * LOOKUPVALUE('Backlog DimUSDExchangeRates'[Value],'Backlog DimUSDExchangeRates'[Curr],FactContractDetails[Curr])Here is the pbix file which might be helpful for you.
i have these two tables FactContractDetails as fact table this table container the currency code and the date and the other table Backlog.DimUSDExchangeRates it contains all the exchange rate for currency for all years. in the fact table it contain the transaction amount for some date and currency code if i want to calculate the amount for selected year i have to comulative all the amounts untill the selected year and this is hard part i have to get the exchange rate for selected year and multiply with total transaction amount.
Hi fmariesh ,
Based on the understanding of your requirement, I have created the Fact and Dimension tables and connected them based in Currency table.
Also created a new date table based on standard M query and also related them based on which you will be able to filter out ypur records.
For the multiplication I created a new calculated column using the below DAX:
column = FactContractDetails[Txn Amount] * LOOKUPVALUE('Backlog DimUSDExchangeRates'[Value],'Backlog DimUSDExchangeRates'[Curr],FactContractDetails[Curr])Here is the pbix file which might be helpful for you.