Many-to-Many Currency Conversion - DAX Error returning final result for Backlog measure

[SQLMonger]

I’ve run into a DAX challenge that has me stumped. This lengthy post describes the business problem to be resolved and the steps I took to come up with and debug the solution. Necessary background information describing the features of the models design, a normal [USD Backlog] measure, currency conversion measures, and the desired [Backlog Amt] measure  are included.

The bottom-line is that the last step of the measure fails, being stopped by an odd DAX error, “The expression specified in the query is not a valid table expression.” I’ve not run into previously and have not been able to find a workaround. Similar measures, including ones in the DAXPatterns.com post Currency conversion – DAX Patterns that was my refrence material. If you have hit this error and have a workaround, skip the rest of this post and let me know. Any ideas and suggestion greatly appreciated.

Task: Implement a “Backlog” measure that works to convert from multiple source currencies to any selected reporting currency.  

A backlog measure totals up all open order amounts prior to a selected reporting date. It is accomplished by determining the maximum [Date] from the Calendar dimension for the current filter context, using that date as the “last date in range” for the window of days to perform the aggregation over. No beginning date is needed for the range, as orders will drop out of the selection as they are shipped or canceled.

This approach requires that there be no ‘active’ relationships between the [Sales Order] fact table and the [Calendar] table. As a result, all regular measures for the table need to specify a relationship to the [Calendar] table to determine which relationship to the [Calendar] to use. Two examples are below, one for [Order Date] and one for [Ship Date].

MEASURE 'Sales Order'[USD Bookings] =
    CALCULATE (
        SUM ( 'Sales Order'[USDExtendedPrice] ),
        USERELATIONSHIP ( 'Sales Order'[Order Date], Calendar[Date] ) 
    )
MEASURE 'Sales Order'[USD Net Shipped] =
    CALCULATE (
        SUM ( 'Sales Order'[ExtendedPrice] ),
        USERELATIONSHIP ( 'Sales Order'[Ship Date], Calendar[Date] ),
        FILTER (
            VALUES ( 'Sales Order'[Line Status] ),
            'Sales Order'[Line Status] = "C"
        )
    )

For [Backlog Amt], the calculation cannot have a relationship to the [Calendar] but does rely on the [Calendar] when slicing in reports. The net effect is that a single [Calendar] dimension supports slicing by multiple date fields without the need for multiple [Calendar] dimensions. The dynamic currency calculation measures below are [Net Bookings] and [Backlog Amt], (not working yet).

Having no active relationships ensures that filter contexts for the [Calendar] table will not directly affect the results of the Backlog measures. Instead, a variable is used to get the “Last Date In Range” needed to set the time range for aggregating open orders in the second step of the measure.

MEASURE 'Sales Order'[USD Backlog] = 
VAR _LastDateInRange =
   CALCULATE(
       MAX( Calendar[Date] ),
       FILTER( ALL( Calendar ), Calendar[Date] = MAX( Calendar[Date] ) 
             && Calendar[Date] <= MAX( Calendar[Date] ) 
       )
   )
VAR _BacklogToDate =
   CALCULATE(
       SUM( 'Sales Order'[USDExtendedPrice] ),
       FILTER( VALUES( 'Sales Order'[Order Date] ),
              'Sales Order'[Order Date] <= _LastDateInRange ),
       FILTER( VALUES( 'Sales Order'[Order Number] ), 
              NOT ( ISBLANK( 'Sales Order'[Order Number] ) ) ),
       FILTER( VALUES( 'Sales Order'[Close Date] ),
                OR( 'Sales Order'[Close Date] > _LastDateInRange, 
                   ISBLANK( 'Sales Order'[Close Date] )
                )
            )
        )
    RETURN  _BacklogToDate

Given a solution for determining the backlog, the next step is to apply currency conversion to the results.

The best Currency Conversion pattern is found on DAXPatterns.com: Currency conversion – DAX Patterns. The Many-to-Many pattern was implemented in this case. It allows for facts to be presented in the source/origin currency and converted to any target currency, using one of the dates on the fact table to drive the selection of the exchange rate.

The below [Net Sales] measure is a good example for this pattern.

The IF() statement is essential to ensure that only a single target currency is selected in the query context, returning an error message if not. The first table variable summarizes the selected rows by [date], [Source Currency], adds the related conversion [Rate], and sums [LOC Net Sales]. The second step checks to make sure a [Rate] value was retrieved and then multiplies the amount and the rate to produce the converted result.

MEASURE 'Sales History'[Net Sales] =

IF(
    NOT ( HASONEVALUE( 'Target Currency'[Target Currency Code] ) ),
    ERROR( "Select a single Target Currency Code" ),
    VAR _AggregateInCurrency =
        ADDCOLUMNS(
            SUMMARIZE(
                'Sales History',
                Calendar[Date],    --Day granularity
                'Source Currency'[Source Currency Code]
            ),
            "@Rate", CALCULATE( SELECTEDVALUE( 'Currency Rate'[Rate] ) ),
            "@LOCNetSalesAmount", [LOC Net Sales]
        )
    VAR _Result =
        SUMX(
            _AggregateInCurrency,
            IF(
                NOT ( ISBLANK( [@Rate] ) ),
                [@LOCNetSalesAmount] * [@Rate],
                ERROR( "Missing conversion rate" )
            )
        )
    RETURN
        _Result
)

This all works great. Where the challenge comes in is in trying to combine these two patterns for the [Backlog Amt] measure.

The code for the hidden [Loc Backlog] measure is below. It works when used independently:

MEASURE 'Sales Order'[Loc Backlog] =
    IF(
        NOT ( HASONEVALUE( 'Source Currency'[Source Currency Code] ) ),
        BLANK( ),
        VAR LastDateInRange =
            CALCULATE(
                MAX( Calendar[Date] ),
                FILTER(
                    ALL( Calendar ),
                    Calendar[Date] = MAX( Calendar[Date] )
                        && Calendar[Date] <= MAX( Calendar[Date] )
                )
            )
        VAR BacklogToDate =
            CALCULATE(
                SUM( 'Sales Order'[LocExtendedPrice] ),
                FILTER(
                    VALUES( 'Sales Order'[Order Date] ),
                    'Sales Order'[Order Date] <= LastDateInRange
                ),
                FILTER(
                    VALUES( 'Sales Order'[Order Number] ),
                    NOT ( ISBLANK( 'Sales Order'[Order Number] ) )
                ),
                FILTER(
                    VALUES( 'Sales Order'[Close Date] ),
                    OR(
                        'Sales Order'[Close Date] > LastDateInRange,
                        ISBLANK( 'Sales Order'[Close Date] )
                    )
                )
            )
        RETURN
            BacklogToDate
    )

The scenario is different in that the [Backlog] measure must be converted using the exchange rate from the reporting date, not any date field available in the report. The following test script works right up to the point of returning the final results. Because it is being ru as a DAX query, variables are used to set the slicer values for ReportDate and TargetCurrency. The variable LastDateInRange is intended for use in the final cube measure script:

// Show the work. Details tie out
DEFINE
    VAR __ReportDate = TREATAS({DATE(2023, 8, 28)}, 'Calendar'[Date])
    VAR __TargetCurrency = TREATAS({"MXN"}, 'Target Currency'[Target Currency Code])
    VAR LastDateInRange = 
        CALCULATE(
            MAX( Calendar[Date] ),
            FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MAX( 'Calendar'[Date] ) )
        )
    VAR __Granularity =
        ADDCOLUMNS (
            SUMMARIZE ('Sales Order', 
                'Source Currency'[Source Currency Code]
            ),
            "@ToCurrency", __TargetCurrency,
            "@Date",  __ReportDate,
            "@Backlog", [Loc Backlog],
            "@Rate",
                LOOKUPVALUE(
                    'Currency Rate'[Rate] ,
                    'Currency Rate'[CalendarDate], __ReportDate,
                    'Currency Rate'[ToCurrencyCode], __TargetCurrency,
                    'Currency Rate'[FromCurrencyCode], 'Source Currency'[Source Currency Code] 
                ),
            "@BacklogConverted", [Loc Backlog] * LOOKUPVALUE(
                    'Currency Rate'[Rate] ,
                    'Currency Rate'[CalendarDate], __ReportDate,
                    'Currency Rate'[ToCurrencyCode], __TargetCurrency,
                    'Currency Rate'[FromCurrencyCode], 'Source Currency'[Source Currency Code]
                )
        )
    VAR Result =  SUMX( __Granularity, [@Rate] * [@Backlog])  
EVALUATE  __Granularity

 The script returns the table variable results and shows that the math works:

Simplifying the script down further, to just the elements that will be needed in the final measure resulted in a cleaner script:

//simplify down to the barest elements
DEFINE
    VAR __ReportDate = TREATAS({DATE(2023, 8, 28)}, 'Calendar'[Date])
    VAR __TargetCurrency = TREATAS({"MXN"}, 'Target Currency'[Target Currency Code])
    
    VAR LastDateInRange = 
        CALCULATE(
            MAX( Calendar[Date] ),
            FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MAX( 'Calendar'[Date] ) )
        )
    VAR AggregatedSalesInCurrency  =
        ADDCOLUMNS (
            SUMMARIZE ('Sales Order', 
                'Source Currency'[Source Currency Code]
            ),
            "@Backlog", [Loc Backlog] * LOOKUPVALUE(
                    'Currency Rate'[Rate] ,
                    'Currency Rate'[CalendarDate], __ReportDate,
                    'Currency Rate'[ToCurrencyCode], __TargetCurrency,
                    'Currency Rate'[FromCurrencyCode], 'Source Currency'[Source Currency Code]
                )
        )
    VAR Result =  SUMX( AggregatedSalesInCurrency, [@Backlog]) // Final step throws an error when returned
EVALUATE AggregatedSalesInCurrency 

Confirming that the final aggregated results are still correct.

The last step, changes the EVALUATE statement to return the ‘Result’ variable instead of the table variable ‘AggregatedSalesInCurrency’. At this point the dream of a solution falls apart. The error message “The expression specified in the query is not a valid table expression.” Results.

Other attempts to return an aggregation of the table variable using other approaches produce the same error.

The final measure does not fail in the cube, but returns only blank values. I did test removing the check in [Loc Backlog] for multiple source currencies, but it did not change the result.

-------------------------
-- Measure: [Backlog Amt]
-------------------------
MEASURE 'Sales Order'[Backlog Amt] = 
    IF (
        NOT ( HASONEVALUE( 'Target Currency'[Target Currency Code] ) ),
        ERROR( "Select a single Target Currency Code" ),
        VAR _LastDateInRange = 
            CALCULATE(
                MAX( Calendar[Date] ),
                FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MAX( 'Calendar'[Date] ))
            )
        VAR AggregatedSalesInCurrency  =
            ADDCOLUMNS (
                SUMMARIZE ('Sales Order', 
                    'Source Currency'[Source Currency Code]
                ),
                "@Backlog", [Loc Backlog] * LOOKUPVALUE(
                        'Currency Rate'[Rate] ,
                        'Currency Rate'[CalendarDate], _LastDateInRange,
                        'Currency Rate'[ToCurrencyCode], SELECTEDVALUE( 'Target Currency'[Target Currency] ),
                        'Currency Rate'[FromCurrencyCode], 'Source Currency'[Source Currency Code]
                    )
            )
        VAR Result =  SUMX( AggregatedSalesInCurrency, [@Backlog] )
      RETURN  Result
    )
    FormatString = "#,##0;(#,##0)"

Seems like it should work, as I have used this pattern of building variables in a measure and then returning a SUMX() from the table variable, but it just does not work in this scenario.

Again, any ideas or suggestions welcomed.

-Clayton

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[SQLMonger]

@Mahesh0016 Thank you for the response.

I tried your suggestion:

MEASURE 'Sales Order'[Backlog Amt Aggregated] = 
    VAR _LastDateInRange = 
            CALCULATE(
                MAX( Calendar[Date] ),
                FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MAX( 'Calendar'[Date] ))
            )
    	VAR AggregatedSalesInCurrency  =
            ADDCOLUMNS (
                SUMMARIZE ('Sales Order', 
                    'Source Currency'[Source Currency Code]
                ),
    			"@Backlog", [Loc Backlog] * LOOKUPVALUE(
    					'Currency Rate'[Rate] ,
    					'Currency Rate'[CalendarDate], _LastDateInRange,
    					'Currency Rate'[ToCurrencyCode], SELECTEDVALUE( 'Target Currency'[Target Currency] ),
    					'Currency Rate'[FromCurrencyCode], 'Source Currency'[Source Currency Code]
    				)
            )
       RETURN  AggregatedSalesInCurrency

MEASURE 'Sales Order'[Backlog Amt] = 
    IF (
        NOT ( HASONEVALUE( 'Target Currency'[Target Currency Code] ) ),
        ERROR( "Select a single Target Currency Code" ),
    	VAR AggregatedSalesInCurrency  = [Backlog Amt Aggregated]
    	VAR Result =  SUMX( AggregatedSalesInCurrency, [@Backlog])
      RETURN  Result
    )

Unfortunately it did not work. I split the measure as you suggested, and both measures throw errors in Tabular Editor:

[Backlog Amt Aggregated] - "MeasureExpression: The measure refers to multiple columns. Multiple columns cannot be converted to a Scalar value."

Which results in [Backlog Amt] throwing an error as well. Just for grins, I deployed and tested each measure using Power BI Desktop, but it does not work there either.

I've tried refactoring the calculation into multiple steps within the measure as well, all with the same result. The inability to convert the table variable to a scalar result.

[Mahesh0016]

@SQLMonger I hope this helps you. thank you.

The error you're encountering, "The expression specified in the query is not a valid table expression," typically occurs when you try to return a scalar value (a single number or text) from a measure, but DAX expects a table as the result. In your case, it's likely happening because you're trying to return a scalar value (Result) from your measure '[Backlog Amt]'.

To resolve this issue and get the expected result, you can follow these steps:

1. **Separate the Measure Calculation and Aggregation**: In your '[Backlog Amt]' measure, perform the calculation of 'AggregatedSalesInCurrency' but don't aggregate it in that measure. Instead, create a separate measure to perform the aggregation.

```DAX
MEASURE 'Sales Order'[Aggregated Backlog Amt] =
VAR _LastDateInRange =
CALCULATE(
MAX( Calendar[Date] ),
FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MAX( 'Calendar'[Date] ))
)
VAR AggregatedSalesInCurrency =
ADDCOLUMNS (
SUMMARIZE ('Sales Order',
'Source Currency'[Source Currency Code]
),
"@Backlog", [Loc Backlog] * LOOKUPVALUE(
'Currency Rate'[Rate] ,
'Currency Rate'[CalendarDate], _LastDateInRange,
'Currency Rate'[ToCurrencyCode], SELECTEDVALUE( 'Target Currency'[Target Currency] ),
'Currency Rate'[FromCurrencyCode], 'Source Currency'[Source Currency Code]
)
)
RETURN
AggregatedSalesInCurrency
```

2. **Create a Separate Aggregation Measure**: Now, create another measure to aggregate the 'AggregatedSalesInCurrency' and return the final result.

```DAX
MEASURE 'Sales Order'[Backlog Amt] =
IF (
NOT ( HASONEVALUE( 'Target Currency'[Target Currency Code] ) ),
ERROR( "Select a single Target Currency Code" ),
VAR AggregatedSalesInCurrency = [Aggregated Backlog Amt]
VAR Result = SUMX( AggregatedSalesInCurrency, [@Backlog] )
RETURN Result
)
```

By separating the calculation and aggregation into two measures, you can avoid the "The expression specified in the query is not a valid table expression" error, and your measure '[Backlog Amt]' should work correctly, returning the aggregated result as expected.