cancel
Showing results for
Did you mean:
New Member

## Two tables(years), same structure, one relation, what function is used in this case?

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.

1 ACCEPTED SOLUTION
Resolver III

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.

-------------------------------------------------------------------
It was useful? Press Thumbs Up!

2 REPLIES 2
Resolver III

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.

-------------------------------------------------------------------
It was useful? Press Thumbs Up!

New Member

Thank you. Very thankful

Announcements

#### Launching new user group features

Learn how to create your own user groups today!