Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
Here's a very simple example. There are 3 students, and there is a date filter on the report.
Student | Course | Exam Date | Result |
S001 | MBA601 | 1/08/2019 | Fail |
S001 | MCS601 | 5/09/2019 | Pass |
S001 | MBA607 | 7/09/2019 | Pass |
S001 | MBA604 | 9/09/2019 | Pass |
S001 | MBA605 | 21/10/2019 | Pending |
S002 | MCS607 | 7/08/2019 | Fail |
S002 | MBA605 | 8/09/2019 | Pass |
S002 | MBA603 | 23/10/2019 | Pending |
S003 | MCS601 | 5/08/2019 | Pass |
S003 | MBA607 | 7/09/2019 | Pass |
S003 | MCS603 | 7/09/2019 | Pass |
S003 | MBA605 | 8/09/2019 | Pass |
S003 | MBA605 | 9/10/2019 | Pass |
I want to create a measure to count the students passed all courses within a certain time frame.
Say if the date filter in from Aug 2019 to Oct 2019, the result should be 1 as only Student S003 has passed all exams in this time frame. But if I adjust the filter to look at Sep 2019 only, the result should be 3 as all 3 students have passed all exams in September.
The measure that I wrote only works then I put it in a table with [Student] column in it. How can I return the value in a card?
Count All Pass = CALCULATE(DISTINCTCOUNT('Table'[Student]), FILTER(ALLEXCEPT('Table', 'Table'[Student]), AND(MAX('Table'[Result]) = "Pass", MIN('Table'[Result]) = "Pass")))
Solved! Go to Solution.
Hi @Penn ,
You can try to use following measure formula if it suitable for your requirement:
Passed Student Count= COUNTROWS ( FILTER ( SUMMARIZE ( ALLSELECTED ( T3 ), [Student], "A Course", DISTINCTCOUNT ( T3[Course] ),//all courses "P Course", CALCULATE ( DISTINCTCOUNT ( T3[Course] ), T3[Result] = "Pass" )//passed courses ), [A Course] = [P Course] ) )
Regards,
Xiaoxin Sheng
Hi @Penn ,
You can try to use following measure formula if it suitable for your requirement:
Passed Student Count= COUNTROWS ( FILTER ( SUMMARIZE ( ALLSELECTED ( T3 ), [Student], "A Course", DISTINCTCOUNT ( T3[Course] ),//all courses "P Course", CALCULATE ( DISTINCTCOUNT ( T3[Course] ), T3[Result] = "Pass" )//passed courses ), [A Course] = [P Course] ) )
Regards,
Xiaoxin Sheng
This is pure witchcraft! Thanks!
Still no clue
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |