DAX - Group by multiple fields from multiple table

Category: sql server powerpivotexcel

Question

DAX_NEWBIE on Thu, 25 Feb 2016 23:07:38


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


Replies

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]
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.

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

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



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


Transaction_ID needs to be in the ROWS of your pivot not in the VALUES.  Try placing it in the ROWS before Currency_Code.

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


BINGO !! You are DA MAN!!

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


Glad I could help!