The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
16 | |
15 |
User | Count |
---|---|
79 | |
32 | |
30 | |
24 | |
21 |