Count occurence of a measure

[Irisohyama6]

Hi all,

I am trying to count how many of occurence from a measure but I don't know why my CALCULATE function didn't work.

Here is my table:

From left to right:

actual table,

count of occurence for each 'Name', 

[# Attendence = Yes] = COUNTROWS(FILTER('Table','Table'[Attendance]="Yes"))

cards for (I use quick measure), the bottom card is below formula with '-1' at the end.

# Max Attended =

MAXX(

    KEEPFILTERS(VALUES('Table'[Name])),

    CALCULATE(COUNTA('Table'[Attendance]))

)

Now I want to insert a card beside those cards to count how many times they occur in the middle table, the attendence for 3 is 1 person, the attendence of 2 is 2 persons.

I tried to use this, but it doesn't work, or maybe I don't understand it.

Count of Occurence =

CALCULATE(

MAX([# Attendence = Yes]), 

FILTER('Table','Table'[Name]))

[tamerj1]

Hi @Irisohyama6 

you may try

Count of Occurence =
SUMX (
    VALUES ( 'Table'[Name] ),
    CALCULATE ( IF ( [# Attendence = Yes] = [# Max Attended], 1, 0 ) )
)

[Irisohyama6]

I got this:

That's probably not right because number of 3 occur only once. 

[tamerj1 · Accepted solution]

Hi @Irisohyama6 
The issue is that you are filtering manually the "Yes" attendance. The auto generated quick measure does not now that therfore it is calculating the max count weather the attendance is "Yes" or "No" (I also did the same mistake in my measure). The other thing is that if you place this measure (The max attendance) in the matrix you will not get the same result in all rows as it does not remove the filter from the names column (actually for some reson which is unknow to me it strangely KEEPFILTERS the values of the Names column. However that can be fixed as follows:

[# Attendence = Yes] = COUNTROWS ( FILTER ( 'Table', 'Table'[Attendance] = "Yes" ) )

# Max Attended = 
CALCULATE ( 
    MAXX (
        CALCULATETABLE ( VALUES('Table'[Name] ), 'Table'[Attendance]  = "Yes" ),
        CALCULATE ( COUNTROWS ('Table') )
    ),
    ALL ('Table'[Name] )
)

Count of Occurence = 
SUMX (
    CALCULATETABLE ( VALUES ( 'Table'[Name] ), 'Table'[Attendance] = "Yes" ),
    CALCULATE ( IF ( [# Attendence = Yes] = [# Max Attended], 1, 0 ) )
)

[Irisohyama6]

Thank you very much.

I knew it was something to do with filters that was missing, but I was unsure where and how I would do it.