How to write a DAX measure based on both current row and slicer context?

Question

Background Info: For each row in an international sales table, I need to retrieve the USD exchange rate at a specific date in the past, so that analysts can determine the impact of changes in exchange rates on sales figures. I will then use the difference between today's exchange rate and this past exchange rate, and multiply it by sales amount to determine the impact.

Actual problem: How to get the following formula to work for each row in the InternationalSales table.

IF( HASONEVALUE(SlicerTable[Date]),
LOOKUPVALUE(ExchangeRates[FX_Rate],
            ExchangeRates[CurrencyCode], InternationalSales[CurrencyCode],
            ExchangeRates[Date], SlicerTable[Date]
            ), 
 BLANK())

Relevant tables are:

'SlicerTable' containing the set of relevant dates in the past in column [Date]. This table is the data source of the slicer.
'ExchangeRates' with columns [CurrencyCode], [Date] and [FX_Rate]. For instance ("EUR", "01/01/2016", 1.143) for the exchange rate EUR to USD on Jan 1
'InternationalSales' with lots of columns but among others [SaleAmount], [Date] and [CurrencyCode]

For each row in InternationalSales, I am trying to look up the exchange rate for the currency of the sale, for the date specified in the slicer.

I have tried many things, but I always end up with the following dilema:

a) I cannot write a calculated column because it is calculated at processing time and the "Date" is selected by the user in a slicer

b) I cannot write a measure because it is not an aggregation, I get error:

"The value for column 'CurrencyCode', in table 'InternationalSales' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified"

Tricia Tricia · Accepted Answer · 2016-03-02T15:29:08

Apparently I got the solution, it was just a matter of placing the formula inside a SUMX, and the slicer value inside a VALUES function.

IF( HASONEVALUE(SlicerTable[Date]),
   SUMX(InternationalSales,
      LOOKUPVALUE(ExchangeRates[FX_Rate],
               ExchangeRates[CurrencyCode], InternationalSales[CurrencyCode],
               ExchangeRates[Date], VALUES(SlicerTable[Date])
               ) * InternationalSales[SaleAmount]
   ), 
 BLANK())

How to write a DAX measure based on both current row and slicer context?

1 Answers