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.
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 |
Solved! Go to 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.
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.
Thanks! This works! I have accepted your answer as the solution.
You are welcome.
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |