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!