Count the number of occurrences in another table

[req77]

Hi,

I have two tables that follow this structure:

Table: Employee

ID	Name
1	John
2	Jack
3	Bob

Table: Feedback

ID	Name
1	John
1	John
2	Jack
2	Jack
2	Jack
3	Bob

I want to know the number of feedbacks (number of rows in the feedback table) for each person (ID), but I want 4 different measures:

1st measure: count of occurrences of IDs in table Feedback that appear at least 1 time.

2nd measure: count of occurrences of IDs in table Feedback that appear at least 1 or 2 times.

3rd measure: count of occurrences of IDs in the Feedback table that appear at least 1, 2 or 3 times.

4th measure: count of occurrences of IDs in the Feedback table that appear at least 1, 2, 3 or 4 times.

So based on the example tables above:

1st measure = 3  (Jhon, Jack and Bob)

2nd measure = 2 (Jhon and Jack)

3nd measure = 1 (Jack)

4nd measure = 0

Can someone help me?

[littlemojopuppy · Accepted solution]

Hi @req77 

Try this...

FeedbackCount = COUNT(Feedback[Name])

Measure XX =
VAR	EmployeeSummary =
	ADDCOLUMNS(
		Employee,
		"FeedbackCount",
		FeedbackCount
	)
RETURN

COUNTROWS(
	FILTER(
		EmployeeSummary,
		FeedbackCount > XX
	)
)

In the second measure (named Measure XX) substitute XX with the count you want to filter for.  Hope this helps!

[CNENFRNL]

Excel worksheet formula is powerful enough to resolve such a simple quesiton,

[bcdobbs · Accepted solution]

Similar solution to @littlemojopuppy (started playing when it came in).

Main difference is no need to build the summary table. Won't make any difference on small datasets but should work faster if you have large amounts of data.

2+ Feedback = 
COUNTROWS (
    FILTER (
        VALUES ( Employee[ID] ),
        CALCULATE ( COUNTROWS ( Feedback ) ) >= 2
    )
)

You could also add a disconnected parameter table with numbers from 1 to n which the measure could read so you don't need multiple versions of the code:

Employees With Feedback = 

VAR SelectedNumber = SELECTEDVALUE ( Parameter[Number] )

VAR Result = 
COUNTROWS (
    FILTER (
        VALUES ( Employee[ID] ),
        CALCULATE ( COUNTROWS ( Feedback ) ) >= SelectedNumber
    )
)

RETURN Result

[littlemojopuppy]

@bcdobbs you're right...I've totally gotten into this habit of creating summary tables.  I need to get out of that...  😉

[littlemojopuppy · Accepted solution]

Hi @req77 

Try this...

FeedbackCount = COUNT(Feedback[Name])

Measure XX =
VAR	EmployeeSummary =
	ADDCOLUMNS(
		Employee,
		"FeedbackCount",
		FeedbackCount
	)
RETURN

COUNTROWS(
	FILTER(
		EmployeeSummary,
		FeedbackCount > XX
	)
)

In the second measure (named Measure XX) substitute XX with the count you want to filter for.  Hope this helps!