Forum Discussion
Pulling out distinct values based on multiple criteria
Hi hajoo125
"430" is my table name.
if 'table name'[year],'table name'[quarter] are columns, try measure below
Measure = CALCULATE(DISTINCTCOUNT('table name'[participants id]),ALLEXCEPT('table name','table name'[year],'table name'[quarter],'table name'[Thematic principale]))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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).