DAX - Group by multiple fields from multiple table

Mike Dietterick on Fri, 26 Feb 2016 22:19:06

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 (
    ADDCOLUMNS (
        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]

Herbert Seidenberg on Fri, 26 Feb 2016 23:42:45

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.
http://www.mediafire.com/download/s9f42qs3ddw104v/02_26_16a.xlsx
http://www.mediafire.com/download/hjh6ded2o9ff1fc/02_26_16a.pdf

DAX_NEWBIE on Mon, 29 Feb 2016 15:45:56

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
CAD                  Canadian 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.

Mike Dietterick on Mon, 29 Feb 2016 16:05:41

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?

DAX_NEWBIE on Mon, 29 Feb 2016 17:04:20

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

Mike Dietterick on Mon, 29 Feb 2016 18:27:26

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.

DAX_NEWBIE on Mon, 29 Feb 2016 19:24:31

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

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.

Mike Dietterick on Mon, 29 Feb 2016 19:32:10

DAX_NEWBIE on Mon, 29 Feb 2016 19:43:11

Mike Dietterick on Mon, 29 Feb 2016 19:49:31