Getting error when Calculating the Sum of premium values using DAX on a slowly changing dimension

Question

Using DAX - I need to calculate the total premium amount for policies with a certain status - using a slowly changing dimension as a source.

This is something that's running within SSAS tabular model.

The solution has two tables - dim_date (which is a calendar table) and a Dim_contract (which have all my policies listed) The Dim_Contract table is a slowly changing dimension.

The table format:

DW_ContractID   DW_EffectiveFromDate    contract_number actual_recurring_collection ContractStatusTLA
-2145825896 22 August    2018   4303140 80  PIN
-2145627139 26 September 2018   4303140 80  INF
-2145428382 09 October   2018   4303140 80  Can
-2145229625 21 August    2018   4303142 100 PIN
-2145030868 22 September 2018   4303142 100 NTU
-2144832111 02 September 2018   4303999 50  PIN
-2144633354 03 September 2018   4303999 50  INF

I've done a calculation to count the policies - which work 100%. With this calc, we count the number of policies for each time period. (depending what the user selects)

Example - if we want to count the number of policies as at the end of September per status we should get:

INF: 2 (This is counting policy numbers 4303140 and 4303999 as their last status is INF)
CAN: 0 
NTU: 1

The code used to do the counts:

PolicyCount_NTU :=
IF (
    MIN ( DIM_Date[Date] )
        >= CALCULATE ( MIN ( DIM_Contract[DW_EffectiveFromDate] ), ALL ( DIM_Contract ) ),
    IF (
        MIN ( DIM_Date[Date] )
            <= CALCULATE ( MAX ( DIM_Contract[DW_EffectiveFromDate] ), ALL ( DIM_Contract ) ),
        CALCULATE (
            COUNTROWS (
                FILTER (
                    DIM_Contract,
                    DIM_Contract[DW_ContractID]
                        = CALCULATE (
                            MAX ( DIM_Contract[DW_ContractID] ),
                            ALL ( DIM_Contract ),
                            DIM_Contract[contract_number] = EARLIER ( DIM_Contract[contract_number] ),
                            ( DIM_Contract[DW_EffectiveFromDate] ) <= VALUES ( DIM_Date[Date] )
                        )
                )
            ),
            LASTDATE ( DIM_Date[Date] ),
            DIM_Contract[ContractStatusTLA] = "NTU"
        )
    )
)

The problem comes in with the calc to get the sum of the amounts... I used this code:

PolicyAPI_NTU :=
IF (
    MIN ( DIM_Date[Date] )
        >= CALCULATE ( MIN ( DIM_Contract[DW_EffectiveFromDate] ), ALL ( DIM_Contract ) ),
    IF (
        MIN ( DIM_Date[Date] )
            <= CALCULATE ( MAX ( DIM_Contract[DW_EffectiveFromDate] ), ALL ( DIM_Contract ) ),
        CALCULATE (
            SUM ( DIM_Contract[actual_recurring_collection] ),
            (
                FILTER (
                    DIM_Contract,
                    DIM_Contract[DW_ContractID]
                        = CALCULATE (
                            MAX ( DIM_Contract[DW_ContractID] ),
                            ALL ( DIM_Contract ),
                            DIM_Contract[contract_number] = EARLIER ( DIM_Contract[contract_number] ),
                            DIM_Contract[DW_EffectiveFromDate] < VALUES ( DIM_Date[Date] )
                        )
                )
            ),
            LASTDATE ( DIM_Date[Date] ),
            DIM_Contract[ContractStatusTLA] = "NTU"
        )
    )
)

Using the last piece of code to get the SUM of the amounts doesn't work - I get an error: Error: Calculation error in measure 'DIM_Contract'[PolicyAPI_NTU]: A table of multiple values was supplied where a single value was expected.

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-26T14:48:35

I suspect your problem is here

DIM_Contract[DW_EffectiveFromDate] < VALUES ( DIM_Date[Date] )

The DAX doesn't know how to do a comparison if DIM_Date[Date] has multiple values.

Try using a MAX or MIN or LASTDATE or something similar instead of VALUES.

Getting error when Calculating the Sum of premium values using DAX on a slowly changing dimension

1 Answers