topic how to return the amount of where the sum of values for each student is 0? in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103666#M109225 <P>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:<BR /><BR />Heres the dataset:&nbsp;<BR /><img /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR />Ive tried to create this measure :<BR /><BR />Measure = CALCULATE(DISTINCTCOUNT(Table1[ID_Student]), FILTER((Table1, SUM((Table1[Value])=0))+0<BR /><BR />But it always returning 0 instead of the expect result which is 2.<BR /><BR /><BR /></P> Tue, 28 Feb 2023 13:24:56 GMT BostonBI 2023-02-28T13:24:56Z how to return the amount of where the sum of values for each student is 0? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103666#M109225 <P>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:<BR /><BR />Heres the dataset:&nbsp;<BR /><img /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR /><BR />Ive tried to create this measure :<BR /><BR />Measure = CALCULATE(DISTINCTCOUNT(Table1[ID_Student]), FILTER((Table1, SUM((Table1[Value])=0))+0<BR /><BR />But it always returning 0 instead of the expect result which is 2.<BR /><BR /><BR /></P> Tue, 28 Feb 2023 13:24:56 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103666#M109225 BostonBI 2023-02-28T13:24:56Z Re: how to return the amount of where the sum of values for each student is 0? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103727#M109231 <P>Hi,</P><P>my solution:</P><DIV><PRE><SPAN>Measure = </SPAN><BR /><BR /><SPAN>VAR</SPAN> <SPAN>__table</SPAN><SPAN> =</SPAN><SPAN>SUMMARIZE</SPAN><SPAN>(</SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>'Table'</SPAN><SPAN>,</SPAN><SPAN>'Table'</SPAN><SPAN>[Value]</SPAN><SPAN>=</SPAN><SPAN>0</SPAN><SPAN>),</SPAN><SPAN>'Table'</SPAN><SPAN>[Student]</SPAN><SPAN>,</SPAN><SPAN>"__count0"</SPAN><SPAN>,</SPAN><SPAN>count</SPAN><SPAN>(</SPAN><SPAN>'Table'</SPAN><SPAN>[Subject]</SPAN><SPAN>))</SPAN><BR /><SPAN>RETURN</SPAN> <SPAN>sumx</SPAN><SPAN>(</SPAN><SPAN>__table</SPAN><SPAN>,</SPAN><SPAN>[__count0]</SPAN><SPAN>)</SPAN></PRE><DIV>&nbsp;</DIV><DIV><img /></DIV><DIV>You write:&nbsp;<BR /><SPAN>But it always returning 0 instead of the expect result which is 2. -&gt; why 2 i count 3 students in your example?&nbsp;</SPAN></DIV></DIV> Tue, 28 Feb 2023 13:25:03 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103727#M109231 andhiii079845 2023-02-28T13:25:03Z Re: how to return the amount of where the sum of values for each student is 0? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103738#M109234 <P>Theres actually 4 students on my example, Which 2 of them got only 0 In theis values. Summarizing:<BR /><BR />I need to return the amount of students which all values Science,Math and English are 0.</P> Tue, 28 Feb 2023 13:29:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103738#M109234 BostonBI 2023-02-28T13:29:59Z Re: how to return the amount of where the sum of values for each student is 0? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103790#M109241 <P>Okay, thank you. Try this:</P><DIV><DIV>&nbsp;</DIV><PRE><SPAN>Measure = </SPAN><BR /><SPAN>VAR</SPAN> <SPAN>__table</SPAN><SPAN> = </SPAN><SPAN>SUMMARIZE</SPAN><SPAN>(</SPAN><SPAN>'Table'</SPAN><SPAN>,</SPAN><SPAN>'Table'</SPAN><SPAN>[Student]</SPAN><SPAN>,</SPAN><SPAN>"__sum0"</SPAN><SPAN>,</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'Table'</SPAN><SPAN>[Value]</SPAN><SPAN>))</SPAN><BR /><SPAN>RETURN</SPAN> <SPAN>COUNTX</SPAN><SPAN>(</SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>__table</SPAN><SPAN>,</SPAN><SPAN>[__sum0]</SPAN><SPAN>=</SPAN><SPAN>0</SPAN><SPAN>),</SPAN><SPAN>[Student]</SPAN><SPAN>)</SPAN></PRE></DIV> Tue, 28 Feb 2023 13:45:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103790#M109241 andhiii079845 2023-02-28T13:45:36Z Re: how to return the amount of where the sum of values for each student is 0? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103935#M109253 <P>You got that Right,Thank you Alot!</P> Tue, 28 Feb 2023 14:54:21 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103935#M109253 BostonBI 2023-02-28T14:54:21Z Re: how to return the amount of where the sum of values for each student is 0? https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103940#M109254 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="507968" data-lia-user-login="BostonBI" class="lia-mention lia-mention-user">BostonBI</a>&nbsp;</P> <P>please try</P> <P>Measure1 =<BR />SUMX (<BR />VALUES ( Table1[ID_Student] ),<BR />INT ( CALCULATE ( SUM ( Table1[Value] ) ) = 0 )<BR />)</P> Tue, 28 Feb 2023 14:59:53 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/how-to-return-the-amount-of-where-the-sum-of-values-for-each/m-p/3103940#M109254 tamerj1 2023-02-28T14:59:53Z