locked
DAX - Group by multiple fields from multiple table RRS feed

  • 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 DAX_NEWBIE Friday, February 26, 2016 1:29 AM
    Thursday, February 25, 2016 11:07 PM

Answers

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

    • Proposed as answer by Michael Amadi Saturday, February 27, 2016 9:07 AM
    • Marked as answer by DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    Friday, February 26, 2016 10:19 PM
    Answerer
  • 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 DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    Monday, February 29, 2016 4:05 PM
    Answerer
  • 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
    Answerer
  • 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 DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    Monday, February 29, 2016 7:32 PM
    Answerer

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

    • Proposed as answer by Michael Amadi Saturday, February 27, 2016 9:07 AM
    • Marked as answer by DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    Friday, February 26, 2016 10:19 PM
    Answerer
  • 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

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

    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 DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    Monday, February 29, 2016 4:05 PM
    Answerer
  • 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 DAX_NEWBIE 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
    Answerer
  • 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 DAX_NEWBIE 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 DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    Monday, February 29, 2016 7:32 PM
    Answerer
  • BINGO !! You are DA MAN!!
    • Marked as answer by DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    • Unmarked as answer by DAX_NEWBIE Monday, February 29, 2016 7:43 PM
    Monday, February 29, 2016 7:43 PM
  • Glad I could help!
    Monday, February 29, 2016 7:49 PM
    Answerer