Forum Discussion
Helper IINeed count with multiple filters
I have created and Append table and need to get some calculations from it.
Table is called Append1 and looks like following:
| TITLE | STUDENT LAST NAME | STUDENT FIRST NAME | DESIGNATION | COURSE | TEACHER | SEMESTER GRADE | SEMESTER |
| 999999 | ADAMS | HENRY | ADV PHY ED | MAHRING | A | SEM 1 | |
| 999999 | ADAMS | HENRY | DRAWING I | BEINTUM | F | SEM 1 | |
| 999999 | ADAMS | HENRY | DRIVER ED CLASS | STEPHENS | B | SEM 1 | |
| 999999 | ADAMS | HENRY | INTR TECH & ENG | RICE | F | SEM 1 | |
| 999999 | ADAMS | HENRY | KEYBOARD FORMAT | QUILTY | A | SEM 1 | |
| 999999 | ADAMS | HENRY | CORE | ENGLISH II | PAINTER | A | SEM 1 |
| 999999 | ADAMS | HENRY | ESSA | GEOMETRY | RENO | B | SEM 1 |
| 999999 | ADAMS | HENRY | ESSA | PHYS SCIENCE | FOGLIANO | F | SEM 1 |
| 222222 | ADAMS | JOHN | APPLIED MANUFAC | RICE | A | SEM 1 | |
| 222222 | ADAMS | JOHN | ARMY JROTC III | Williams | D | SEM 1 | |
| 222222 | ADAMS | JOHN | CAREER PLAN | HUBERT | F | SEM 1 | |
| 222222 | ADAMS | JOHN | CORE | AM GOVERNMENT | PHILLIPS | D | SEM 1 |
| 222222 | ADAMS | JOHN | CORE | AP ENGLISH LIT | RHOADES | D | SEM 1 |
| 222222 | ADAMS | JOHN | CORE | ECONOMICS | QUILTY | F | SEM 2 |
| 333333 | AGUILERA | CHRISTINA | ARMY JROTC III | Williams | C | SEM 1 | |
| 333333 | AGUILERA | CHRISTINA | DRIVER ED CLASS | STEPHENS | F | SEM 1 | |
| 333333 | AGUILERA | CHRISTINA | ELL TUTORIAL | HINRICHSEN | F | SEM 1 | |
| 333333 | AGUILERA | CHRISTINA | ESL II | BAILEY | C | SEM 1 | |
| 333333 | AGUILERA | CHRISTINA | SPAN HRT SPK II | HINRICHSEN | F | SEM 1 | |
| 333333 | AGUILERA | CHRISTINA | CORE | US HISTORY | BAILEY | C | SEM 1 |
| 333333 | AGUILERA | CHRISTINA | ESSA | BIOLOGY | CMCKENZIE | B | SEM 1 |
| 333333 | AGUILERA | CHRISTINA | ESSA | GEOMETRY | SZECHOWYCZ | F | SEM 1 |
Need some DAX give me the following:
| TITLE | STUDENT LAST NAME | STUDENT FIRST NAME | COURSE FAILURES | SEMESTER GRADE | SEMESTER |
| 999999 | ADAMS | HENRY | 3 | F | SEM 1 |
Was thinking I could replicate this for SEM 2 in another table/measure.
Thx, Community!
Take a look at the following slhangen - just one measure I think is all you need:
Course Failures = COUNTX( FILTER( 'Table', 'Table'[SEMESTER GRADE] = "F" ), 'Table'[SEMESTER GRADE] )Simply add the fields I have added to a Table visual, then add the Course Failures measure I created. It will calculate the correct numbers per semester.
If that is not what you need, could you be more specific in the output you require and the logic to get there?
10 Replies
- edhans
Community Champion
Take a look at the following slhangen - just one measure I think is all you need:
Course Failures = COUNTX( FILTER( 'Table', 'Table'[SEMESTER GRADE] = "F" ), 'Table'[SEMESTER GRADE] )Simply add the fields I have added to a Table visual, then add the Course Failures measure I created. It will calculate the correct numbers per semester.
If that is not what you need, could you be more specific in the output you require and the logic to get there?
slhangenThis is amazing Ed, but I really need the following:
TITLE STUDENT LAST NAME STUDENT FIRST NAME COURSE FAILURES- SEM 1 COURSE FAILURES- SEM 2
Can you help with this? I would be forever on your debt, lol.
- edhans
Sure, that will require two measures:
Course Failures Semester 1 = COUNTX( FILTER( 'Table', 'Table'[SEMESTER GRADE] = "F" && 'Table'[SEMESTER] = "SEM 1" ), 'Table'[SEMESTER GRADE] )and
Course Failures Semester 2 = COUNTX( FILTER( 'Table', 'Table'[SEMESTER GRADE] = "F" && 'Table'[SEMESTER] = "SEM 2" ), 'Table'[SEMESTER GRADE] )Then I got rid of the Semester field in the visual and added both of these measures, and I get this:
Alternatively, you can keep my original measure and use a MATRIX visual instead:
It has these fields:
You need to do a few things to the matrix to make it work and look like I have it:
- Turn off stepped layout
- Turn off row subtotals
- Drill all the way down on the matrix using the single-to-double down arrow when you hover over the matrix.
-
- I like the matrix solution better because it is 1 measure and the semesters would dynamically expand (summer summester for example) without having to write a 3rd semester measure.
But that is the cool thing about Power BI slhangen - different ways to do the same thing. Take your pick!