Need count with multiple filters

[slhangen]

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!

[edhans]

Glad I was able to help @slhangen - hope your project moves forward smoothly.

[edhans · Accepted solution]

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?

[slhangen]

This 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!

[slhangen]

I keep getting an error with this code. 
Table is called Append1 (See screenshot).

Course Failures Semester 1 =
COUNTX(
FILTER(
'Append1',
'Append1'[SEMESTER GRADE]="F"
&& Append1[SEMESTER]="SEM 1"
),
'Append1'[SEMESTER GRADE]
)
 

Am I typing everything correct?

THx so much for your time.

[edhans]

There was no screenshot. What is the error? The only other suggestion I have with the info I have is to use this:

Course Failures Semester 1 =
COUNTAX(
    FILTER(
        'Append1',
        'Append1'[SEMESTER GRADE] = "F"
            && Append1[SEMESTER] = "SEM 1"
    ),
    'Append1'[SEMESTER GRADE]
)

COUNTAX can operate on a boolean data type, but COUNTX won't return an error, it just won't count the true/false values. But if you have text in the semester grade field, it shouldn't matter.

Can you confirm that you have set the field data types correctly? [Semester Grade] and [Semester] should be text. In Power Query (The transform button in the home ribbon), ensure the icon in the upper left is ABC, not ABC/123.

If it is ABC123, click it and select Text. Do that for every column. No columns brought into Power BI should he set to ABC123.

Otherwise, you will need to share actual files via Dropbox or OneDrive that I can take a look at.

[slhangen]

Thx so much for your help on this.   Below is a link for some sample data,

https://www.dropbox.com/scl/fi/2nrenbotacx7d72q1rt6w/1-8-SAMPLE-DATA.xlsx?dl=0&rlkey=dqd7sl6ng56zb41...

Thx again, you are a life saver!

[slhangen]

[edhans]

@slhangen you don't create a table. You create a measure. Then you create a table visual. See my PBIX file here. 

[slhangen]

Thx so miuch, got it working.  Appreciate all of your time and effort.  Thx again!!!