cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markvdb_ITMAT Frequent Visitor
Frequent Visitor

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
3 REPLIES 3
KHorseman Super Contributor
Super Contributor

Re: count where an id in a column occurs more than once

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? Please mark it as a solution.

Proud to be a Datanaut!
Community Support Team
Community Support Team

Re: count where an id in a column occurs more than once

hi, @markvdb_ITMAT 

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.
markvdb_ITMAT Frequent Visitor
Frequent Visitor

Re: count where an id in a column occurs more than once

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 439 members 4,343 guests
Please welcome our newest community members: