Forum Discussion

fmariesh's avatar
fmariesh
Frequent Visitor
5 years ago
Solved

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.DimUSDExchangeR...
Show and Tell
  • PC2790's avatar
    PC2790
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.