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.

PC2790's avatar
PC2790
Community Champion
5 years ago

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

fmariesh's avatar
fmariesh
Frequent Visitor
5 years ago

hi PC2790  please note that my requirment not only in year it will be slicer for i have claculate for each year selected