"The column either doesn't exist or doesn't have a relationship" Error for a single table dataset

[Anonymous]

I have a table having customer transactions. I need to create a new column where, for each row:

1. If its the first appearance of a 'customer id', it will have "Onboarding"
2. If its the last appearance of a 'customer id', it will have "Offboarding"
3. The rest will be "Existing"

So, following is the DAX Code i tried out:

Customer Churn =
IF (
    Book[Date]
        = LOOKUPVALUE ( Book[Date].[Date], Book[customer id], FIRSTDATE ( Book[Date] ) ),
    "Onboarding",
    IF (
        Book[Date]
            = LOOKUPVALUE ( Book[Date].[Date], Book[customer id], LASTDATE ( Book[Date] ) ),
        "Offboarding",
        "Existing"
    )
)

But i am getting the following error:

The column 'Book[customer id]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Can't think of using RELATED() as I'm referring the same table.

Where is the issue? Any workaround if it's not possible to code it this way?

Thanks!

[kaushikd · Accepted solution]

Here you go:-

Download

Below is the dataset I have used

Customer Id	Date
Cust10302692	4/25/2017 0:00
Cust10302721	5/12/2017 0:00
Cust10302735	5/18/2017 0:00
Cust10302692	5/25/2017 0:00
Cust10302721	6/12/2017 0:00
Cust10302735	6/18/2017 0:00
Cust10302692	7/25/2017 0:00
Cust10302721	8/12/2017 0:00
Cust10302735	8/18/2017 0:00

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Solution based your formula could be like below.

Customer Churn =
VAR startDate =
    CALCULATE ( FIRSTDATE ( Book[Date] ), ALLEXCEPT ( Book, Book[Customer Id] ) )
VAR endDate =
    CALCULATE ( LASTDATE ( Book[Date] ), ALLEXCEPT ( Book, Book[Customer Id] ) )
RETURN
    IF (
        Book[Date]
            = LOOKUPVALUE (
                Book[Date],
                Book[customer id], [Customer Id],
                Book[Date], startDate
            ),
        "Onboarding",
        IF (
            Book[Date]
                = LOOKUPVALUE (
                    Book[Date],
                    Book[customer id], [Customer Id],
                    Book[Date], endDate
                ),
            "Offboarding",
            "Existing"
        )
    )

Best Regards,

Dale

[Anonymous]

Thanks a lot!

Both the solutions are working, but for @kaushikd's solution, I create a measure instead of a column. So to use this categorization in visuals like pie/donut charts, i need to create another coumn i.e dataset[column] = dataset[measure]. It is still ok as an additional step but it creates a dependency on the execution of measures. As measure sometimes don't work when there is a dax error in any other calculated column, hence i feel it's more vulnerable. @v-jiascu-msft's solution seems more appropriate here.

Is my understanding correct or both are equally efficient?  

[Anonymous]

Any comments on why the original script i wrote had the error? I still fail to undertand.

Thanks

[v-jiascu-msft]

Hi @Anonymous,

I don't have access to @kaushikd's solution. So I don't know if both are equal. Please refer to dax/lookupvalue-function-dax, you will find out the errors in your original script. The <search_columnName> and <search_value> are a pair. 

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)  

So your formula can't return the right result.

LOOKUPVALUE ( Book[Date].[Date], Book[customer id], FIRSTDATE ( Book[Date] ) )

Best Regards,

Dale

[kaushikd]

Try the below measure:-

Status =
Var A= Max(Book[CustomerId])
Var B= CALCULATE(MAX(Book[Date]),ALL(Book),Book[CustomerId]=A)
Var C= Max(Book[CustomerId])
Var D= CALCULATE(MIN(Book[Date]),ALL(Book),Book[CustomerId]=A)
Var E= SELECTEDVALUE(Book[Date])
Return IF(E=D,"Onboarding",IF(E=B,"Offboarding","Existing"))

[Anonymous]

Hi Kasushik,

I tried your solution, but all are being categorised as "Existing" here.

[kaushikd]

Hi

I tried the same in my side and I am getting the desired result as shown in the picture. Could you please share a sample file with small dataset I will impleament that.

[Anonymous]

Sure.

Here is the link for a sample file: Sample File

[kaushikd]

The sample you have provided, all the customers have appeared once and the Status is showing 'Onboarding' for each customer id.

Is this what you requred...?

[Anonymous]

Oh ok, actually the data set is more than 700k rows. So the sampling must have captured only the first occurence of each id.

For testing purpose can you just copy some consecutive ids and paste them in consecutive rows below?

[kaushikd · Accepted solution]

Here you go:-

Download

Below is the dataset I have used

Customer Id	Date
Cust10302692	4/25/2017 0:00
Cust10302721	5/12/2017 0:00
Cust10302735	5/18/2017 0:00
Cust10302692	5/25/2017 0:00
Cust10302721	6/12/2017 0:00
Cust10302735	6/18/2017 0:00
Cust10302692	7/25/2017 0:00
Cust10302721	8/12/2017 0:00
Cust10302735	8/18/2017 0:00