Forum Discussion
how to implement subquery in power bi
how could i convert the below query to dax ?
select ProjectCode,sum(amount)amount from (select project.ProjectCode,trans.Amount*
(select rate.MultiplicationExchangeRate from Backlog.DimUSDExchangeRates rate where rate.Year=2020 and rate.CurrencyCode=trans.ContractCurrencyCode )amount
from Backlog.FactContractDetails trans
left join Backlog.DimProject project on
trans.ProjectID=project.ProjectID
where year(trans.date)<=2020
)a
group by ProjectCode
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.
5 Replies
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.
fmariesh
As PC2790 said, those queries can be achieve in Power BI, but not in statements format. It can only be achieved step by step, using both power query editor and power bi desktop.And to calculate for each year, you can create dax column something like:
column = calculate(sum(amount),allexept(table,[Year]))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
