Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

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!

2 ACCEPTED SOLUTIONS

Here you go:-

Download

 

Below is the dataset I have used

 

Customer IdDate
Cust103026924/25/2017 0:00
Cust103027215/12/2017 0:00
Cust103027355/18/2017 0:00
Cust103026925/25/2017 0:00
Cust103027216/12/2017 0:00
Cust103027356/18/2017 0:00
Cust103026927/25/2017 0:00
Cust103027218/12/2017 0:00
Cust103027358/18/2017 0:00

View solution in original post

v-jiascu-msft
Employee
Employee

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"
        )
    )

the_column_either

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-jiascu-msft
Employee
Employee

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"
        )
    )

the_column_either

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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

Thanks

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kaushikd
Resolver II
Resolver II

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"))

 

 

 

Capture.JPG

Anonymous
Not applicable

Hi Kasushik,

 

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

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
Not applicable

Sure.

 

Here is the link for a sample file: Sample File

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
Not applicable

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?

Here you go:-

Download

 

Below is the dataset I have used

 

Customer IdDate
Cust103026924/25/2017 0:00
Cust103027215/12/2017 0:00
Cust103027355/18/2017 0:00
Cust103026925/25/2017 0:00
Cust103027216/12/2017 0:00
Cust103027356/18/2017 0:00
Cust103026927/25/2017 0:00
Cust103027218/12/2017 0:00
Cust103027358/18/2017 0:00

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.