Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have a table having customer transactions. I need to create a new column where, for each row:
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!
Solved! Go to Solution.
Here you go:-
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 |
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
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
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?
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
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"))
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.
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...?
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:-
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |