Counting first instance of a number in a column

[justinbouchard]

Hi everyone,

I'm working with a student dataset where it shows all students who register in a given term. It also incldues the term the student is admitted. What I am trying to do is get a number of how many students were admitted in a given term.

Here is a sample dataset

Studend ID	Term of Registration	Term Admitted
123	Fall 2019	Fall 2018
124	Fall 2019	Fall 2018
125	Fall 2019	Fall 2019
123	Winter 2020	Fall 2018
124	Winter 2020	Fall 2018
125	Winter 2020	Fall 2019
123	Fall 2021	Fall 2018
124	Fall 2021	Fall 2018
125	Fall 2021	Fall 2019

If I filter down so only one term of registration is showing this is simple distinct count. But when i have multiple terms of regisrationg showing it will repeatedly count the values in the column of term admitted. 

I know i can run separate measures for each specific admit term, but I'm looking to have one foruma so i can graph this.

Whats the best way to do this with dax?

Thank you,

Justin

[daXtreme]

What you say is simply not true:

But when i have multiple terms of regisrationg showing it will repeatedly count the values in the column of term admitted.

Distinct count will count any student only once in any data set. That's how distinct count works.

[justinbouchard]

Yes, I was unclear on that. I guess I want to do a distinct count within a distinct count

[Syk]

Try this...

Students =
SUMX (
    SUMMARIZE ( Students, Students[Term of Registration] ),
    DISTINCTCOUNT ( Students[Studend ID] )
)

[daXtreme · Accepted solution]

Dear @Syk 

Why use SUMMARIZE when simple DISTINCT/VALUES is enough? Your code can be written simpler:

Students =
SUMX(
    DISTINCT( Students[Term of Registration] ),
    CALCULATE( 
        DISTINCTCOUNT( Students[Studend ID] ) 
    )
)

and you forgot CALCULATE.

On the other hand, this is precisely what @justinbouchard did not want because your code counts a student many times on the total row.