topic Re: Need count with multiple filters in DAX Commands and Tips

Need count with multiple filters

slhangen — Thu, 07 Jan 2021 18:09:38 GMT

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!

Re: Need count with multiple filters

edhans — Thu, 07 Jan 2021 19:11:36 GMT

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?

Re: Need count with multiple filters

slhangen — Thu, 07 Jan 2021 20:57:41 GMT

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.

Re: Need count with multiple filters

edhans — Thu, 07 Jan 2021 22:06:34 GMT

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!

Re: Need count with multiple filters

slhangen — Thu, 07 Jan 2021 23:03:43 GMT

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.

Re: Need count with multiple filters

edhans — Fri, 08 Jan 2021 00:24:19 GMT

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.

Re: Need count with multiple filters

slhangen — Fri, 08 Jan 2021 21:48:37 GMT

Re: Need count with multiple filters

slhangen — Fri, 08 Jan 2021 22:04:32 GMT

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=dqd7sl6ng56zb414ntsf08mnf

Thx again, you are a life saver!

Re: Need count with multiple filters

edhans — Sat, 09 Jan 2021 00:56:40 GMT

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

Re: Need count with multiple filters

slhangen — Mon, 11 Jan 2021 17:34:07 GMT

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

Re: Need count with multiple filters

edhans — Mon, 11 Jan 2021 19:02:30 GMT

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