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.
Hi fmariesh ,
Before we move onto tranforming the query to DAX, I want to understand the end outcome that you are expecting out of it. As most of the operations in this query can be taken care as simple drag and drop operations in Power BI.
For example:
1) SQL: condition Year = 2020
Power BI: A simple filter with year as 2020will do the trick
2) SQL: Left join two tables
Power BI: Merging of two tables in Power Query will solve the purpose. It totally depends on thDe requirment. It might be possible that building appropriate relationship between two tables is sufficient.
3) SQL: Group by
Power BI: Summarize or Group by DAX can be used
So understanding the expected outcome will help formulate a substantial solution for your requirement
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.