topic Re: Count of Students that exist in all terms selected in DAX Commands and Tips

Count of Students that exist in all terms selected

SHullen — Tue, 16 Apr 2024 23:55:48 GMT

I have a datasource with Student information. I'd like to count the unique students that exist in all selected terms. I have a test calculation that kind-of works. The correct answer is 2 but my report gives me 2 for all races that I add. I need the calculation to only show results for the students of each race/gender who have records in all terms.
Here's my DAX code and the results I'm getting. In this example, only Student 11111 should be counted.

Term_Cnt_F = CONVERT(CALCULATE(DISTINCTCOUNT('dw vw_Exec_Course_Performance'[Term])
,ALLSELECTED('dw vw_Exec_Course_Performance') )

- CALCULATE(DISTINCTCOUNT('dw vw_Exec_Course_Performance'[StudentID])
,FILTER(ALLSELECTED('dw vw_Exec_Course_Performance')

,'dw vw_Exec_Course_Performance'[StudentID]=MAX('dw vw_Exec_Course_Performance'[StudentID]))) ,INTEGER)

Thanks to anyone who can help me with this!!

Re: Count of Students that exist in all terms selected

wdx223_Daniel — Wed, 17 Apr 2024 04:54:50 GMT

=VAR _terms=ALLSELECTED('dw vw_Exec_Course_Performance'[Term]) RETURN COUNTROWS(FILTER(VALUES('dw vw_Exec_Course_Performance'[StudentID]),ISEMPTY(EXCEPT(_terms,CALCULATTABLE(VALUES('dw vw_Exec_Course_Performance'[Term]))))))

Re: Count of Students that exist in all terms selected

Anonymous — Wed, 17 Apr 2024 06:26:06 GMT

Hi SHullen ,

Thanks for the reply from wdx223_Daniel .

Please try this modified measure:

Term_Cnt_F = VAR SelectedTermsCount = DISTINCTCOUNT('dw vw_Exec_Course_Performance'[Term]) VAR StudentsInAllTerms = CALCULATETABLE( SUMMARIZE( 'dw vw_Exec_Course_Performance', 'dw vw_Exec_Course_Performance'[StudentID], "TermsCount", CALCULATE(DISTINCTCOUNT('dw vw_Exec_Course_Performance'[Term])) ), ALLSELECTED('dw vw_Exec_Course_Performance') ) RETURN COUNTROWS( FILTER( StudentsInAllTerms, [TermsCount] = SelectedTermsCount ) )

Is this correct?

If you only select 11111 it looks like this:

pbix file is attached.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Count of Students that exist in all terms selected

SHullen — Wed, 17 Apr 2024 08:28:26 GMT

Thank you so much Daniel!!! This works perfectly as expected.

Re: Count of Students that exist in all terms selected

SHullen — Wed, 17 Apr 2024 08:31:55 GMT

Thank you so much for responding!! Daniel's code worked perfectly as I needed it to but I will review your code when I have a chance.