cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

count where an id in a column occurs more than once

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

MVB
11 REPLIES 11
Community Support

hi, @Anonymous

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:

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

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Community Champion

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)

Frequent Visitor

Thanks for that!

What about if I just want a single value!

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

Community Champion

Hi @ITH ,

Can you share some sample data .

Regards,

HN

Frequent Visitor

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

Community Champion

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)

Frequent Visitor

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

Community Champion

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)

Frequent Visitor

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

Community Champion

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(
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(
Dim_Students,
"events",
CALCULATE(
DISTINCTCOUNT(IT_Exclusion_Primary[External Id])
)
),
[events] = 0
)
),
CALCULATE(
DISTINCTCOUNT(Dim_Students[StudentId]),
ALL(Dim_Students)
)
)```

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors