how to return the amount of where the sum of values for each student is 0?

[BostonBI]

Heres the situation, according to my dataset(made one fast on Excel to speed things up) i need to retrieve the amount of students which the sum of values is equal to 0:

Heres the dataset: 











Ive tried to create this measure :

Measure = CALCULATE(DISTINCTCOUNT(Table1[ID_Student]), FILTER((Table1, SUM((Table1[Value])=0))+0

But it always returning 0 instead of the expect result which is 2.

[andhiii079845 · Accepted solution]

Okay, thank you. Try this:

Measure = 
VAR __table = SUMMARIZE('Table','Table'[Student],"__sum0",SUM('Table'[Value]))
RETURN COUNTX(FILTER(__table,[__sum0]=0),[Student])

[tamerj1 · Accepted solution]

Hi @BostonBI 

please try

Measure1 =
SUMX (
VALUES ( Table1[ID_Student] ),
INT ( CALCULATE ( SUM ( Table1[Value] ) ) = 0 )
)

[andhiii079845 · Accepted solution]

Okay, thank you. Try this:

Measure = 
VAR __table = SUMMARIZE('Table','Table'[Student],"__sum0",SUM('Table'[Value]))
RETURN COUNTX(FILTER(__table,[__sum0]=0),[Student])

[BostonBI]

You got that Right,Thank you Alot!

[andhiii079845]

Hi,

my solution:

Measure = 

VAR __table =SUMMARIZE(FILTER('Table','Table'[Value]=0),'Table'[Student],"__count0",count('Table'[Subject]))
RETURN sumx(__table,[__count0])

You write: 
But it always returning 0 instead of the expect result which is 2. -> why 2 i count 3 students in your example? 

[BostonBI]

Theres actually 4 students on my example, Which 2 of them got only 0 In theis values. Summarizing:

I need to return the amount of students which all values Science,Math and English are 0.