Pulling out distinct values based on multiple criteria

[hajoo125]

Hello Everyone,

Please i need some helping writing some dax function to pull distinct values from a live data feed and based on multiple criteria into a new table. There is a lot of duplicate data that needs to be filtered out. Its a training attendance form and customers are required to undergo just one training on a particular topic per quarter. But we are having customers being recorded multiple times for the same training within the same quarter. 

[v-juanli-msft]

Hi @hajoo125 

Create a measure

Measure = CALCULATE(DISTINCTCOUNT('430'[customers]),ALLEXCEPT('430','430'[quarter],'430'[training]))

Add columns and measures in a matrix visual, you could also expand down all level

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.

[hajoo125]

Hi Maggie,

Thanks for trying to help out. I still haven't gotten this right, can you give me direct syntax for this? What does '430' in your formula mean? See below a snapshot of my report headers in power bi desktop.

So i need to extract unique participants id within each quarter on a given set of training modules.

Thematic principale = training module

[v-juanli-msft]

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.

[hajoo125]

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:

[hajoo125]

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).