Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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
 
Thanks in advance
MVB
11 REPLIES 11
v-lili6-msft
Community Support
Community Support

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

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

 

Capture.JPG

Hi @Anonymous ,

 

 

 

 

1.jpg

 

 

 

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)
 

 

 

2.JPG

 

 

Regards,
Harsh Nathani

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

 

Thanks for that!  

 

What about if I just want a single value!

 

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

harshnathani
Community Champion
Community Champion

Hi @ITH ,

 

Can you share some sample data .

 

Regards,

HN

 

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

 

image.png

 

 

harshnathani
Community Champion
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)

 

 

 

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
Community Champion
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)

ITH
Frequent Visitor

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

KHorseman
Community Champion
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(
        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)
	)
)




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

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.