DAX code change from calculated column to a measure

Question

I have a fact table with settlement_date, product_id, service_id, location_id, and ticket_id and srv_adjusted_earning columns.

I have determined the DAX query to generate a calculated column that sums the srv_adjusted_earning column over the date range: settlement date and settlement date - 27 days (i.e. a 4 week window) as:

=CALCULATE(
    SUM(factService[SRV_ADJUSTED_EARNING]),
    DATESBETWEEN
    (
        factService[SETTLEMENT_DATE],
        DATEADD(factService[SETTLEMENT_DATE], -27, DAY), 
        factService[SETTLEMENT_DATE]
    ),
    FILTER(factService, factService[PRO_ID] = EARLIER(factService[PRO_ID])),
    FILTER(factService, factService[SER_ID] = EARLIER(factService[SER_ID])),
    FILTER(factService, factService[LOC_ID_SELLING] = 
        EARLIER(factService[LOC_ID_SELLING])),
    FILTER(factService, factService[TIS_ID] = EARLIER(factService[TIS_ID]))
)

I am trying to convert this DAX calculated column to a measure and I tried the following:

blob:=CALCULATE
(
    SUM(factService[SRV_ADJUSTED_EARNING]),
    DATESBETWEEN
    (
        factService[SETTLEMENT_DATE], 
        DATEADD(factService[SETTLEMENT_DATE], -27, DAY), 
        factService[SETTLEMENT_DATE]
    ),
    ALLEXCEPT(factService, factService[PRO_ID]),
    ALLEXCEPT(factService, factService[SER_ID]),
    ALLEXCEPT(factService, factService[LOC_ID_SELLING]),
    ALLEXCEPT(factService, factService[TIS_ID])
)

But I get: Error: Calculation error in measure 'factService'[blob]: A single value for column 'SETTLEMENT_DATE' in table 'factService' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Anybody know how I fix this?

Alexis Olson Alexis Olson · Accepted Answer · 2019-03-26T14:51:23

As the error mentions, the issue is with factService[SETTLEMENT_DATE]. In the measure, there is no row context so that it knows which date you are talking about, so you need to specify it somehow. I'd suggest using a variable along these lines:

blob :=
VAR SettleDate = MAX ( factService[SETTLEMENT_DATE] )
RETURN
    CALCULATE (
        SUM ( factService[SRV_ADJUSTED_EARNING] ),
        DATESBETWEEN (
            factService[SETTLEMENT_DATE],
            SettleDate - 27,
            SettleDate
        ),
        ALLEXCEPT (
            factService,
            factService[PRO_ID],
            factService[SER_ID],
            factService[LOC_ID_SELLING],
            factService[TIS_ID]
        )
    )

Here the variable picks the maximal settlement date in the current filter context. If that's not exactly what you need, adjust the definition accordingly.

DAX code change from calculated column to a measure

1 Answers