count where an id in a column occurs more than once

[Anonymous]

Hi

I have a list of students who have received a specific behaviour event, some students have received this multiple times so appear in multiple rows

I can see how many rows there are, i.e. the number of times this specific event has been recorded but I need to know;

1) How many students have at least 1 row attributed to them and have done this (I think) by

Measure = DISTINCTCOUNT(IT_Exclusion_Primary[External Id])

2) How many students appear more than once, i.e. >=2 times

3) I then need to calculate each of these totals into a % out of the wider student population, i.e in Q1 I might have 70 pupils with at least 1 row, I need to calculate 70 out of the total student cohort number

Thanks in advance

MVB

[v-lili6-msft]

hi, @Anonymous 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

For your case, I think just need to use ALLSELECTED Function conditional in your measure formula.

Best Regards,

Lin

[Anonymous]

Here is a grab of the data table. Its the External Id column that is repeated in rows, as you will see. Some pupils appear once, others several times. I can find out how many students appear, what I can;t seem to get is how many of the pupils appear more than once, ">=2" Thanks

[harshnathani]

Hi @Anonymous ,

You can use these measures.

Distinct Count of External Id = DISTINCTCOUNT('Table'[External ID])

Distinct Count greater than 1 =

var _dc = DISTINCTCOUNT('Table'[External Id])

Return

IF (_dc >1 , _dc)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ITH]

Thanks for that!  

What about if I just want a single value!

example, "6" students signed in more than once.

[harshnathani]

Hi @ITH ,

Can you share some sample data .

Regards,

HN

[ITH]

Very similar to above really,

Just want a card with the number of students that appear more than once on the list. 

example "6" Students with 2+ incidents

Im so over thinking this now that im going around and around

[harshnathani]

Hi @ITH

In the Data provided there are 5 students with more than 1 incident.

You can use this measure

Student Count =
VAR _b =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Student ID] ),
FILTER (
'Table',
CALCULATE (
COUNT ( 'Table'[Student ExclusionID] ),
ALLEXCEPT (
'Table',
'Table'[Student ID]
)
) > 1
)
)
RETURN
_b

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ITH]

Sorry being a pain now

Thanks great thanks!  I only screen shot, some of the data because its a couple of years worth.

Its works great for the whole 'studentexclusions' table, giving me "19" 

however, when i copy and add a slicer for the current academic year total, I get 12 and I know that the answer is 6..  

[harshnathani]

HI @ITH ,

Do not know where is the issue in your data. Its working fine in the screenshot attached.

Try sharing some sample data by masking information.  And pls share in text format.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ITH]

Im having exactly the same issue but with exclusions data, did you manage to work it out?

[KHorseman]

You're correct that your solution to 1 should work.

Is IT_Exclusion_Primary[External Id] the student's ID or an ID for each behavior event? I'm going to assume behavior event. If not, change reference to it in my solution to IT_Exclusion_Primary[EventID] or whatever.

There may be a couple ways to handle this but first I'm going to link to another thread I just posted to: here. You could use the same basic method I'm using there, but instead of filtering with the "= 0" condition you would use ">= 2".

Again, like in that thread I'm assuming you have some sort of student dimension table connected to this IT_Exclusion_Primary table. For your case it sounds like you're not concerned with dates, but the code should be the same either way.

CALCULATE(
    DISTINCTCOUNT(Dim_Students[StudentId]),
    FILTER(
        ADDCOLUMNS(
            Dim_Students,
            "events",
            CALCULATE(
                DISTINCTCOUNT(IT_Exclusion_Primary[External Id])
            )
        ),
        [events] >= 2
    )
)

After you have the basic count of students with >= 2 entries, you should be able to get a % by dividing that by something like:

CALCULATE(
	DISTINCTCOUNT(Dim_Students[StudentId]),
	ALL(Dim_Students)
)

DIVIDE(
	CALCULATE(
		DISTINCTCOUNT(Dim_Students[StudentId]),
		FILTER(
			ADDCOLUMNS(
				Dim_Students,
				"events",
				CALCULATE(
					DISTINCTCOUNT(IT_Exclusion_Primary[External Id])
				)
			),
			[events] = 0
		)
	),
	CALCULATE(
		DISTINCTCOUNT(Dim_Students[StudentId]),
		ALL(Dim_Students)
	)
)