Forum Discussion
Pulling out distinct values based on multiple criteria
Hi Maggie,
Thanks so much. This works quite well except that i need the function to stretch its scope across all columns because we have two columns that account for beneficiary ids and two columns for training modules. This present function is scanning a single column of each, how can i expand it please? See below for present function and what the headers in the file look like.
Participants = CALCULATE(DISTINCTCOUNT('Sheet1'[Participant ID]),ALLEXCEPT('Sheet1',Sheet1[Year],'Sheet1'[Quarter],'Sheet1'[Thématique Principale]))
Table:
Hi Maggie,
So i have been inspecting this function deeply and realizing that it might not give me exactly what i want. Check the image below out:
In the sheet above, we have about 29 rows that shows that beneficiaries have attended multiple trainings and a beneficiary can attend the same training multiple times. There are two columns for beneficiary ids, one for those that came to the training with their registration cards and those who didn't. Also there are two columns for training modules because more than one training module can be taught at any session.
To calculate for the number of beneficiaries who have attended a GAP training in a quarter would be the count of the first instance only of each beneficiary that has attended either GEP,GMR or INP. So we do not count duplicates and in the sheet above the correct answer would be 6.
6 beneficiaries have attended GAP trainings in the quarter(highlighted in green).