Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning. I have a problem to solve, if you can help me. I have two tables (different year: 2018-2019) with the same structure. I have two important variables in each of those tables : Clients and year. I want to identify two groups :
The clients that come again in 2019 and classify them as continuators
Customers who do not come in 2019 : classify them as lost.
Any idea how? Thank you very much.
Solved! Go to Solution.
Hi,
It was quite fun to solve your Problem.
At First two sample Tables based on your Information.
The Data Inside of the Tables.
Then I build up a new calculated Table I call it Clients. With all Unique Clients as the first Column with this Dax Code.
Clients = DISTINCT(UNION(ALLSELECTED('Client-Year2018'[Client]);ALLSELECTED('Client-Year2019'[Client])))
After that you can calculate for each unique Client if it present or not with the Dax Code below in a new Calculated column.
I Modified it a bit to know if a customer comes new in 2019.
Classification =
var present2018 = LOOKUPVALUE('Client-Year2018'[Client]; 'Client-Year2018'[Client]; Clients[Client])
var present2019 = LOOKUPVALUE('Client-Year2019'[Client]; 'Client-Year2019'[Client]; Clients[Client])
Return
IF(present2018 = Clients[Client] && present2019 = Clients[Client]; "continuators"; IF(present2018 = Clients[Client] && NOT(present2019 = Clients[Client]); "Lost"; IF(NOT(present2018 = Clients[Client]) && present2019 = Clients[Client]; "New")))
The new Table looks like this.
I hope this will help.
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
Hi,
It was quite fun to solve your Problem.
At First two sample Tables based on your Information.
The Data Inside of the Tables.
Then I build up a new calculated Table I call it Clients. With all Unique Clients as the first Column with this Dax Code.
Clients = DISTINCT(UNION(ALLSELECTED('Client-Year2018'[Client]);ALLSELECTED('Client-Year2019'[Client])))
After that you can calculate for each unique Client if it present or not with the Dax Code below in a new Calculated column.
I Modified it a bit to know if a customer comes new in 2019.
Classification =
var present2018 = LOOKUPVALUE('Client-Year2018'[Client]; 'Client-Year2018'[Client]; Clients[Client])
var present2019 = LOOKUPVALUE('Client-Year2019'[Client]; 'Client-Year2019'[Client]; Clients[Client])
Return
IF(present2018 = Clients[Client] && present2019 = Clients[Client]; "continuators"; IF(present2018 = Clients[Client] && NOT(present2019 = Clients[Client]); "Lost"; IF(NOT(present2018 = Clients[Client]) && present2019 = Clients[Client]; "New")))
The new Table looks like this.
I hope this will help.
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
Thank you. Very thankful
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |