Using DAX for Currency Conversion

Andrew Smith Posted on

As part of my DAX studies I’m pulling apart DAX expressions written by experts in the field and understanding: (i) how they work, (ii) why they’re a good solution and (iii) how they relate to the tables and relationships that underpin their calculations.

In this post I’m analysing one of the currency conversion queries demonstrated by Christian Wade in his MDX vs. DAX: Currency Conversion Faceoff SQLBits session. The query is this:

daxcurrencyconvquery

and an excerpt of the results is:

daxcurrencyconvqueryresults

Stepping back from the query to begin with, the currency conversion model under consideration is based upon the many-to-many relationship shown here:

daxcurrencyconvmodel

and where the table layout in the SSDT SSAS tabular project looks like:

daxcurrencyconvtables

The main complexity in the DAX query shown previously is with regards calculating the End of Day Rate for January as a whole, which needs to be the rate of the last day for which a value exists, which for this query is 31st January 2006.

I’m going to analyse the query from the inside out, so first we have:

SUMMARIZE(
   ‘Date’,
   ‘Date'[Month],
   ROLLUP(‘Date'[Date]),
   “End of Day Rate”, [End of Day Rate LastNonBlank Formatted]
),

The DAX SUMMARIZE() function plays a similar role to the GROUP BY clause in SQL – it takes one or more columns from a table and performs the aggregation specified in the final argument(s) over all combinations of them. The ‘Date'[Month] column is confined to January for 2006, since that’s been defined in the filter context in the outer CALCULATETABLE() function. The use of ROLLUP() here works similarly to its SQL equivalent in that it adds rows to create aggregations of the aggregations (i.e. working from right to left in the ROLLUP() list for when there are multiple columns). Here it’s used to create the row showing the End of Day Rate for January as a whole.

The SUMMARIZE() function can take multiple name/expression pairs to define the aggregated data that should result, but here we just have the single [End of Day Rate LastNonBlank Formatted] measure, that’s defined at the top of the query.

Stepping out from the SUMMARIZE() function, we have the CALCULATETABLE() function next:

CALCULATETABLE(
   SUMMARIZE(
      ‘Date’,
      ‘Date'[Month],
      ROLLUP(‘Date'[Date]),
      “End of Day Rate”, [End of Day Rate LastNonBlank Formatted]
   ),
   ‘Date'[Calendar Year] = 2006,
   ‘Date'[Month] = “January”,
   ‘Currency'[Currency Name] = “United Kingdom Pound”
)

Very simply this evaluates a table expression in a context modified by the given filters – the latter being the filters on ‘Date'[Calendar Year], ‘Date'[Month] and ‘Currency'[Currency Name] as shown above. This then becomes the basis for the further day-based filtering that’s applied by SUMMARIZE().

Relationships in DAX flow from the ‘many’ to the ‘one’ side of a relationship (only), so when the query’s measures on the Currency Rate table are evaluated, the Currency Key relationship from it to ‘Currency'[Currency Name] is used to filter its rows by “United Kingdom Pound” as specified in the CALCULATETABLE() function and the Date Key relationship from it to ‘Date'[Calendar Year] and ‘Date'[Month] is used to filter its rows further by 2006 and “January”.

Lastly we look at the measures defined in the query:

MEASURE ‘Currency Rate'[End of Day Rate Sum] =
      SUM(‘Currency Rate'[End of Day Rate])
MEASURE ‘Currency Rate'[End of Day Rate LastNonBlank] =
      [End of Day Rate Sum](LASTNONBLANK(‘Date'[Date], [End of Day Rate Sum]))
MEASURE ‘Currency Rate'[End of Day Rate LastNonBlank Formatted] =
      FORMAT([End of Day Rate LastNonBlank], “0.00”)

As Christian Wade noted in his presentation these would usually be defined in the model rather than in the query (e.g. for reuseability resaons) but for the purposes of explanation it makes sense here to make them part of the query that uses them.

Consistent with best practice the final measure – which is the only one that would be visible to the user – is built up in steps from the 2 prior measures. The first of these defines a SUM() aggregation over ‘Currency Rate'[End of Day Rate], which essentially does nothing since because of the query context it will only ever be the sum of one number. It’s necessary though (or else another numeric aggregation such as MIN() or MAX()) since we need the value to come from a measure so that we can apply the query’s filter context to it, and measures are inherently aggregation devices.

The second measure uses the LASTNONBLANK() function to return the last date for which the [End of Day Rate Sum] measure is not blank. At the day level using this function has no effect, but it does come into play when calculating the End of Day Rate for January as a whole. Then, it uses its knowledge of the ordering of the Date data type used by the ‘Date'[Date] column specified as its first argument, to locate the last day of the month with End of Day Rate data.

As Marco Russo says in his blog post here, the first argument to LASTNONBLANK() above is implicitly encased in the VALUES() function. It can in fact be rewritten explicitly using VALUES():

MEASURE ‘Currency Rate'[End of Day Rate LastNonBlank] =
      [End of Day Rate Sum](LASTNONBLANK(VALUES(‘Date'[Date]), [End of Day Rate Sum]))

This makes it more obvious that LASTNONBLANK() is executing over a table of date values as defined by the current filter context, which for January 2006 as a whole is all the days in January.

The syntax that Christian used for this measure:
      [End of Day Rate Sum](LASTNONBLANK(‘Date'[Date], [End of Day Rate Sum]))

is just shorthand for use of the CALCULATE() function. I.e. in its long format it would be:
      CALCULATE([End of Day Rate Sum], LASTNONBLANK(‘Date'[Date], [End of Day Rate Sum]))

The third measure specified in the query just formats the second measure to 2 decimal places.

So that wraps up the explanation of this query. The presentation that this was taken from (see link above) is worth watching to see the DAX vs MDX comparison for this query as well as for several others.