Distinct count "file no's" grouped by "client id"

[Anonymous]

Dear community!

I hope you can help me. I need to calculate the unique occurences of 'DECISION_NO' grouped by 'CLIENT_ID'. What I ultimately want to achieve is to calculate whether a client is "recurring". I think I can do this by subtracting the number of distinct 'DECISION_NO' from the number of times a 'CLIENT_ID' occurs in the dataset. If this number is more than 0, the client is recurring (see the column 'RETURNING_CLIENT' in mock data below)

I have managed 'count_ID' as a calculated column, but I struggle with 'count_decision': The number of distinct 'DECISION_NO' grouped by 'CLIENT_ID'

Some context with the dataset

I have a dataset where every individual record refers to a 'CARE PRODUCT'. These care products are tied to decisions and files. 

A client can be linked to multiple files (minimum of 1);

A file can be linked to multiple decisions (minimum of 1) and products (minimum of 1)

So if there is a client in the dataset, there is at least 1 file_no and 1 decision_no.

I hope you people can help me out.

CLIENT_ID	FILE_NO	DECISION_NO	START_CARE	END_CARE	CARE_PRODUCT	count_ID	count_decision	returning_CLIENT
100	201	10	1-1-2018	1-2-2018	A1	4	3	1
100	201	10	1-1-2018	1-2-2018	B2	4	3	1
100	202	11	1-4-2018	1-5-2018	A2	4	3	1
100	202	12	12-4-2018	1-6-2018	B2	4	3	1
125	305	36	1-5-2018	1-7-2018	C1	1	1	0
135	307	44	1-7-2018	1-8-2018	A3	2	2	0
135	307	44	1-7-2018	1-8-2018	B1	2	2	0
140	333	51	1-8-2018	1-9-2018	A1	3	2	1
140	333	51	1-8-2018	1-9-2018	B2	3	2	1
140	333	52	1-8-2018	1-10-2018	C2	3	2	1

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(DISTINCTCOUNT(Data[DECISION_NO]),FILTER(Data,Data[CLIENT_ID]=EARLIER(Data[CLIENT_ID])))

For Client ID 35 the answer should be 1.

Hope this helps.

[Anonymous]

Thanks! This works! I have accepted your answer as the solution.

[Ashish_Mathur]

You are welcome.