Counting the Results of a Measure - How to Not Count Blanks

[duesouth]

I'm working on a Power BI Dashboard for a school.  I've got a measure in my model that returns how far above/below their Target grade a pupil is, which looks something like:

Pupil 1      0

Pupil 2      0.5

Pupil 3      -0.5

Pupil 4      1

Pupil 5     

Some pupils (pupil 5 in this example), who are absent over a long term or temporarily at another school, will not have a current grade, so the return for the +/- Target measure is blank.

I've written a measure to count how many pupils are above target - and it seems it is also counting the pupils with a blank return in the +/- Target measure.

Vs Target Above = COUNTX(FILTER(VALUES('KS3 Assessment 2023 2024'[External Id]) ,[+/- Target] >0), [External Id])

How can I amend the above expression to avoid counting the blank cells? The external Id field is the id of each pupil and is a unique field.

Many thanks in advance for any help!

[HotChilli · Accepted solution]

I got it. Spent some time looking at it yesterday (too much time).

I agree with you that there should be a simpler way.  I'm not happy with these measures, I think that transforming the data will make it a lot easier.

What I did was to split the data table into 2 tables (Target and Result) and then Merge the 2 tables together so that 1 row represents 1 student/1 subject.  You can tell me whether that is a legitimate strategy or the real data is more complex than the sample data and this course of action doesn't make sense.

--

I created a column for the 'score - target' and used that to create one simple measure (COUNTROWS) - which can be filtered for the X value and/or 'score-target' < using the filter pane.

--

Let me know what you think.

[duesouth]

Thank you for taking the time to look at this - I really appreciate it.  I think that method makes a lot of sense - it should be something I can do with the real data as it's the same format, just more of it.  It will make things much easier and efficient.  Thanks again for taking so much time on this.

[duesouth]

If the dropbox thing didn't work, the data is:

External Id	Year	Result	Result date	Resultset	Aspect Type	Aspect Name	Subject
1	7	Me	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery Ad	Art and Design
1	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target Ad	Art and Design
2	7	Me+	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery Ad	Art and Design
2	7	Co	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target Ad	Art and Design
3	7	Em+	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery Ad	Art and Design
3	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target Ad	Art and Design
4	7	Me+	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery Ad	Art and Design
4	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target Ad	Art and Design
5	7	X	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery Ad	Art and Design
5	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target Ad	Art and Design
1	7	Me	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery En	English
1	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target En	English
2	7	Me+	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery En	English
2	7	Co	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target En	English
3	7	Em+	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery En	English
3	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target En	English
4	7	Me+	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery En	English
4	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target En	English
5	7	X	10/04/2024	Year 7 Spring	WAG	SC KS3 Mastery En	English
5	7	Me	10/04/2024	KS3 Target	KS3 Target	SC KS3 Target En	English

Mastery Level	Lookup	Mastery Description
P1	1	P1
P2	1.5	P2
P3	2	P3
Em	2.5	Emerging
Em+	3	Emerging+
De	3.5	Developing
De+	4	Developing+
Me	4.5	Meeting
Me+	5	Meeting+
Co	5.5	Confident
Co+	6	Confident+
Ma	6.5	Mastered
X	0	X

Measures:

KS3 WAG =

calculate(FIRSTNONBLANK('KS3 Assessment 2023 2024'[Result], 1), filter('KS3 Assessment 2023 2024','KS3 Assessment 2023 2024'[Aspect Type]= "WAG" ) )

KS3 WAG Lookup =

SUMX(FILTER('KS3 Assessment 2023 2024',[Aspect Type]="WAG" && 'KS3 Assessment 2023 2024'[Result] <> "X"),[Result Lookup])

KS3 Target =

calculate(FIRSTNONBLANK('KS3 Assessment 2023 2024'[Result], 1), filter('KS3 Assessment 2023 2024','KS3 Assessment 2023 2024'[Aspect Type]= "KS3 Target") )

KS3 Target Lookup =

SUMX(FILTER('KS3 Assessment 2023 2024',[Aspect Type]="KS3 Target" && 'KS3 Assessment 2023 2024'[Result] <> "X"),[Result Lookup])

+/- Target = IF('KS3 Assessment 2023 2024'[KS3 WAG Lookup]>0, [KS3 WAG Lookup] - [KS3 Target Lookup], "")

Vs Target Above =

COUNTX(FILTER(VALUES('KS3 Assessment 2023 2024'[External Id]) ,[+/- Target Test] >0 ), [External Id])

Vs Target At =

COUNTX(FILTER(VALUES('KS3 Assessment 2023 2024'[External Id]) ,[+/- Target Test] =0), [External Id])

Vs Target Below =

COUNTX(FILTER(VALUES('KS3 Assessment 2023 2024'[External Id]) ,[+/- Target Test] <0), [External Id])

[HotChilli]

I don't think GDPR applies to sample data (not real data) posted on a forum site.

What are the data types involved?  [KS3 WAG Lookup] - is that a measure ?

[+/- Target] can return a number or "" and then the measure is compared to zero.   

- I suspect this is the problem.  "" is not blank and it's not a number.  There was mention in the original post about

"the return for the +/- Target measure is blank" . It's not blank (which is special in DAX), it's an empty string and then the comparison (if it's working OK which I haven't really looked at) is comparing to zero.

---

I think I'm going to suggest that you alter your measures with removing any mentions of "" (empty string) because I think that's adding complexity which you do not need.  Try blank() instead of "" in the measure [+/- Target] . This may cause Pupil 5  to be dropped from the visual (depends if you have other fields in the visual which you haven't shown us).  You can get round that by using 'Show items with no data' dropdown option.

[duesouth]

Thanks for the reply.  I had the same thought driving in this morning.

When I put the Blank () into the expression, that returns the right number for above target - but the at target one is then wrong.

I've created a sample file minus all the pupil data and copied the process I've done to date (link below - first time using dropbox, hopefully you can download). 

https://www.dropbox.com/scl/fi/hxws6pf5pkg77n9ma6ja3/Example-for-Plus-Minus-Target.pbix?rlkey=6j4vcf... 

The first thing I did was to separate the "WAG" grades (working at grade, basically a current grade) from the "KS3 Target" ones which are their targets with "KS3 WAG" and "KS3 Target".  Then I've converted the text to a number with "KS3 WAG Lookup" and "KS3 Target Lookup".  Next was the "+/- Target" measure which was producing the expected results.  Finally, I've got three measures so I can see how many pupils are above, at or below their target - so "Vs Target Above", "Vs Target At" and "Vs Target Below".

For Art and Design in the the sample file, I'm expecting to see 1 pupil above target, 1 pupil at target and 2 pupils below target.  The pupil with the X grade is what goes into our MIS for a non-attender and there should be no +/- target calculation.

I might well be coming at this all the wrong way as I've still got an Excel mindset to some degree.  In Excel, this is so easy - you just have a VLOOKUP for the grades and you can ignore the X with an IF statement or IFERROR.  I've tried so many different ways to re-produce this in Power BI, but hit problems.  I had the targets and current grades in separate tables, but just couldn't get the +/- target calculation to work at all - I'm pretty close doing it this way, just the one last thing to figure out!

Thanks for taking the time to help.

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[HotChilli]

What's the measure [+/- Target]? And what does the data look like?

[duesouth]

So our pupils have what we call Mastery level for current grade and target. So, Developing, Developing+, Emerging, Emerging+ etc. etc. up to Mastered.  I've got another table with a number associated to each - so it starts at 0.5 and goes up by 0.5 each time.  The +/- Target is the current grade - target grade - it's basically replicating what we currently use in Excel, but now wish to do in Power BI.

So a pupil with Meeting current grade and Meeting target grade would be 0.  If it helps, the expression for it is:

+/- Target = IF('KS3 Assessment 2023 2024'[KS3 WAG Lookup]>0, [KS3 WAG Lookup] - [KS3 Target Lookup], "")

Tough to post any data due to GDPR...