Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks to anyone who can help me with this!!
Solved! Go to Solution.
=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]))))))
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!
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.
=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]))))))
Thank you so much Daniel!!! This works perfectly as expected.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |