# DAX - Group by multiple fields from multiple table

• ### Question

• Hello Gurus,

This is my day1 with DAX and I am trying to get myself familiarized with Sum, Sumx Calculate, Summarize etc. And then this CURVEBALL(??) is thrown to me.

I am trying to achieve the following from the 2 tables:

TRANSACTION

CURRENCY            AMOUNT
USD                      100
INR                      2000
GBP                     80

CONVERSION

CURRENCY        TARGET_CURRENCY        CONVERSION_FACTOR

USD                              USD                             1
USD                              INR                             60

USD                              GBP                            .6
INR                               USD                           0.1667

INR                               INR                             1
INR                             GBP                              .01

GBP                             USD                             1.6667
GBP                             GBP                             1

GBP                             INR                              100

I am trying to do following through DAX

Select T.CURRENCY, C.TARGET_CURRENCY, SUM(T.AMOUNT*C.CONVERSION_FACTOR)
FROM TRANSACTION T
JOIN CONVERSION C ON T.CURRENCY = C.CURRENCY
GROUP BY T.CURRENCY, C.TARGET_CURRENCY

I would appreciate any help with this.

Thanks

• Edited by Friday, February 26, 2016 1:29 AM
Thursday, February 25, 2016 11:07 PM

• The best solution to this is likely reworking the model based on your desired final report.

For example, I assume in the real world, the Transaction table would really have many duplicate values in the Currency column in which case you wouldn't be able to directly join the 2 tables because the many to many relationship isn't supported.  You would need to create a "bridge" table of unique currency values and relate both tables to that.  Then with the combination of your pivot layout and a DAX measure you could reproduce the table produced by your SQL query. That would likely be the simplest DAX approach

However if you wanted to produce this SQL result table purely as a DAX query (which would require Power BI Desktop, Dax Studio, or an SSAS Tabular model), without any relationships between the tables, you could use something like this:

```EVALUATE
SUMMARIZE (
SUMMARIZE (
CROSSJOIN ( 'Transaction', 'Conversion' ),
'Transaction'[CURRENCY],
'Conversion'[TARGET_CURRENCY],
"T.Amount", SUM ( 'Transaction'[AMOUNT] )
),
"NewAmount", [T.Amount]
* LOOKUPVALUE (
'Conversion'[CONVERSION_FACTOR],
'Conversion'[CURRENCY], [CURRENCY],
'Conversion'[TARGET_CURRENCY], [TARGET_CURRENCY]
)
),
[CURRENCY],
[TARGET_CURRENCY],
[NewAmount]
)
ORDER BY
'Transaction'[CURRENCY],
'Conversion'[TARGET_CURRENCY]```
Obviously this is more complex than the SQL query, so it would probably be better to use relationships, pivot tables, and measures to get a similar result if possible in your particular project.

• Proposed as answer by Saturday, February 27, 2016 9:07 AM
• Marked as answer by Monday, February 29, 2016 7:43 PM
Friday, February 26, 2016 10:19 PM
• EVALUATE means you are running a DAX QUERY which typically returns a table.

Power Pivot in Excel only does MEASURES or CALCULATED COLUMNS which are both scalar values.  To execute a query against a Power Pivot model, you would need to use the DAX STUDIO addin for Excel.

I think to help you any further, you need to describe what result you are trying to achieve.  Are you trying to create a pivot in Excel that mimics the SQL Query result?

• Marked as answer by Monday, February 29, 2016 7:43 PM
Monday, February 29, 2016 4:05 PM
• This query is a little different than the original SQL query since you are now also returning AMOUNT in the result set. So, originally you were grouping CURRENCY and summing the AMOUNT and now it looks like you really want an individual row for each transaction.  This will work in a pivot unit you have an identical CURRENCY and AMOUNT in multiple transactions.  Without some unique ID in the row, that can be used in the pivot, identical transactions would be grouped.

All that being said if both TRANSACTION and CONVERSION are now related to Currency_Bridge, you should set up your pivot like this:

ROWS:

Currency_Bridge[Currency_Code]

Conversion[TARGET_CURRENCY]

Transaction[AMOUNT]

Create a measure like this for converted_amount and add it to the VALUES of your pivot:

```=CALCULATE (
SUM ( 'Transaction'[AMOUNT] ) * SUM ( 'Conversion'[CONVERSION_FACTOR] ),
'Currency_Bridge'
)```

Now under the PivotTable Design tab, adjust the Report Layout to "Show in Tabular Form" and "Repeat All Item Labels"

This should give you a pivot that resembles your second SQL query as long as the Transaction table doesn't have identical rows.  It it has identical rows, they will appear in a single row as an aggregated amount.

Monday, February 29, 2016 6:27 PM
• Transaction_ID needs to be in the ROWS of your pivot not in the VALUES.  Try placing it in the ROWS before Currency_Code.
• Marked as answer by Monday, February 29, 2016 7:43 PM
Monday, February 29, 2016 7:32 PM

### All replies

• The best solution to this is likely reworking the model based on your desired final report.

For example, I assume in the real world, the Transaction table would really have many duplicate values in the Currency column in which case you wouldn't be able to directly join the 2 tables because the many to many relationship isn't supported.  You would need to create a "bridge" table of unique currency values and relate both tables to that.  Then with the combination of your pivot layout and a DAX measure you could reproduce the table produced by your SQL query. That would likely be the simplest DAX approach

However if you wanted to produce this SQL result table purely as a DAX query (which would require Power BI Desktop, Dax Studio, or an SSAS Tabular model), without any relationships between the tables, you could use something like this:

```EVALUATE
SUMMARIZE (
SUMMARIZE (
CROSSJOIN ( 'Transaction', 'Conversion' ),
'Transaction'[CURRENCY],
'Conversion'[TARGET_CURRENCY],
"T.Amount", SUM ( 'Transaction'[AMOUNT] )
),
"NewAmount", [T.Amount]
* LOOKUPVALUE (
'Conversion'[CONVERSION_FACTOR],
'Conversion'[CURRENCY], [CURRENCY],
'Conversion'[TARGET_CURRENCY], [TARGET_CURRENCY]
)
),
[CURRENCY],
[TARGET_CURRENCY],
[NewAmount]
)
ORDER BY
'Transaction'[CURRENCY],
'Conversion'[TARGET_CURRENCY]```
Obviously this is more complex than the SQL query, so it would probably be better to use relationships, pivot tables, and measures to get a similar result if possible in your particular project.

• Proposed as answer by Saturday, February 27, 2016 9:07 AM
• Marked as answer by Monday, February 29, 2016 7:43 PM
Friday, February 26, 2016 10:19 PM
• Excel 2010 with free PowerPivot and PowerQuery Add-Ins.
Compatible with Office 2013/2016 Pro Plus.
PowerQuery's GUI automatically generates "M" code,
and loads Table into PowerPivot Model.

Friday, February 26, 2016 11:42 PM
• Thank you for your response.

I can run your suggested DAX  from SSMS against Tabular model but for some reason I can't put this in PowerPivot. (Sorry I am onDay2 with PowerPivot and DAX)  Is there an issue with Evaluate inside PowerPivot and it can't be put there?

As you  have suggested I have created a bridge table Currency_Bridge and it has the following values

Currency_Code  Currency Name
USD                  US Dollar
GBP                  Great Britain Pound
INR                   Indian Rupees
AUD                  Australian Dollar

And in my Transaction table I have added few more rows.

CURRENCY            AMOUNT
USD                      100
INR                       2000
GBP                      80
USD                      500
GBP                      900
INR                       5000
GBP                      1000

I appreciate any suggestion with this.

Monday, February 29, 2016 3:45 PM
• EVALUATE means you are running a DAX QUERY which typically returns a table.

Power Pivot in Excel only does MEASURES or CALCULATED COLUMNS which are both scalar values.  To execute a query against a Power Pivot model, you would need to use the DAX STUDIO addin for Excel.

I think to help you any further, you need to describe what result you are trying to achieve.  Are you trying to create a pivot in Excel that mimics the SQL Query result?

• Marked as answer by Monday, February 29, 2016 7:43 PM
Monday, February 29, 2016 4:05 PM
• I am expecting I will be able to add a Calculated Measure in PowerPivot/ Tabular Data Model and then use that when connect from excel.

Following is equivalent in SQL. I want to create a pivot in Excel that mimics the SQL Query result when connected to the model:

Select T.CURRENCY,C.TARGET_CURRENCY,T.AMOUNT,SUM(T.AMOUNT*C.CONVERSION_FACTOR) converted_amount

FROM TRANSACTION T

JOIN CONVERSION C ON T.CURRENCY =C.CURRENCY

GROUP BY T.CURRENCY,C.TARGET_CURRENCY,T.AMOUNT

which returns:

CURRENCY TARGET_CURRENCY AMOUNT converted_amount
GBP GBP 80 80
GBP GBP 900 900
GBP GBP 1000 1000
GBP INR 80 8000
GBP INR 900 90000
GBP INR 1000 100000
GBP USD 80 133.336
GBP USD 900 1500.03
GBP USD 1000 1666.7
INR GBP 2000 200
INR GBP 5000 500
INR INR 2000 2000
INR INR 5000 5000
INR USD 2000 333.4
INR USD 5000 833.5
USD GBP 100 60
USD GBP 500 300
USD INR 100 6000
USD INR 500 30000
USD USD 100 100
USD USD 500 500

• Edited by Monday, February 29, 2016 6:24 PM
Monday, February 29, 2016 5:04 PM
• This query is a little different than the original SQL query since you are now also returning AMOUNT in the result set. So, originally you were grouping CURRENCY and summing the AMOUNT and now it looks like you really want an individual row for each transaction.  This will work in a pivot unit you have an identical CURRENCY and AMOUNT in multiple transactions.  Without some unique ID in the row, that can be used in the pivot, identical transactions would be grouped.

All that being said if both TRANSACTION and CONVERSION are now related to Currency_Bridge, you should set up your pivot like this:

ROWS:

Currency_Bridge[Currency_Code]

Conversion[TARGET_CURRENCY]

Transaction[AMOUNT]

Create a measure like this for converted_amount and add it to the VALUES of your pivot:

```=CALCULATE (
SUM ( 'Transaction'[AMOUNT] ) * SUM ( 'Conversion'[CONVERSION_FACTOR] ),
'Currency_Bridge'
)```

Now under the PivotTable Design tab, adjust the Report Layout to "Show in Tabular Form" and "Repeat All Item Labels"

This should give you a pivot that resembles your second SQL query as long as the Transaction table doesn't have identical rows.  It it has identical rows, they will appear in a single row as an aggregated amount.

Monday, February 29, 2016 6:27 PM
• This is awesome and really helpful and I think we are almost there. Only problem I am having is All same currency transactions are getting grouped. This is what I get

 Currency_Code TARGET_CURRENCY Sum of   CONVERSION_FACTOR Amt GBP GBP 1 1980 GBP INR 100 198000 GBP USD 1.6667 3300.066 INR GBP 0.1 700 INR INR 1 7000 INR USD 0.1667 1166.9 USD GBP 0.6 360 USD INR 60 36000 USD USD 1 600

You have correctly understood I really want individual row for each transaction. So that I can group and sum it as needed.

I have now added a Column in the Transaction table with Unique Transaction_ID. But if I add that to the Pivot it does not separate the transactions either.

 Transaction_ID CURRENCY AMOUNT 1 USD 100 2 INR 2000 3 GBP 80 4 USD 500 5 GBP 900 6 INR 5000 7 GBP 1000

 Currency_Code TARGET_CURRENCY Sum of   CONVERSION_FACTOR Sum of   Transaction_ID Amt GBP GBP 1 15 1980 GBP INR 100 15 198000 GBP USD 1.6667 15 3300.066 INR GBP 0.1 8 700 INR INR 1 8 7000 INR USD 0.1667 8 1166.9 USD GBP 0.6 5 360 USD INR 60 5 36000 USD USD 1 5 600

• Edited by Monday, February 29, 2016 7:31 PM
Monday, February 29, 2016 7:24 PM
• Transaction_ID needs to be in the ROWS of your pivot not in the VALUES.  Try placing it in the ROWS before Currency_Code.
• Marked as answer by Monday, February 29, 2016 7:43 PM
Monday, February 29, 2016 7:32 PM
• BINGO !! You are DA MAN!!
• Marked as answer by Monday, February 29, 2016 7:43 PM
• Unmarked as answer by Monday, February 29, 2016 7:43 PM
Monday, February 29, 2016 7:43 PM