Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
duesouth
Regular Visitor

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

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!
1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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.

View solution in original post

8 REPLIES 8
HotChilli
Super User
Super User

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.

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
Regular Visitor

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

 

External IdYearResultResult dateResultsetAspect TypeAspect NameSubject
17Me10/04/2024Year 7 SpringWAGSC KS3 Mastery AdArt and Design
17Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target AdArt and Design
27Me+10/04/2024Year 7 SpringWAGSC KS3 Mastery AdArt and Design
27Co10/04/2024KS3 TargetKS3 TargetSC KS3 Target AdArt and Design
37Em+10/04/2024Year 7 SpringWAGSC KS3 Mastery AdArt and Design
37Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target AdArt and Design
47Me+10/04/2024Year 7 SpringWAGSC KS3 Mastery AdArt and Design
47Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target AdArt and Design
57X10/04/2024Year 7 SpringWAGSC KS3 Mastery AdArt and Design
57Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target AdArt and Design
17Me10/04/2024Year 7 SpringWAGSC KS3 Mastery EnEnglish
17Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target EnEnglish
27Me+10/04/2024Year 7 SpringWAGSC KS3 Mastery EnEnglish
27Co10/04/2024KS3 TargetKS3 TargetSC KS3 Target EnEnglish
37Em+10/04/2024Year 7 SpringWAGSC KS3 Mastery EnEnglish
37Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target EnEnglish
47Me+10/04/2024Year 7 SpringWAGSC KS3 Mastery EnEnglish
47Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target EnEnglish
57X10/04/2024Year 7 SpringWAGSC KS3 Mastery EnEnglish
57Me10/04/2024KS3 TargetKS3 TargetSC KS3 Target EnEnglish

 

Mastery LevelLookupMastery Description
P11P1
P21.5P2
P32P3
Em2.5Emerging
Em+3Emerging+
De3.5Developing
De+4Developing+
Me4.5Meeting
Me+5Meeting+
Co5.5Confident
Co+6Confident+
Ma6.5Mastered
X0X

 

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
Super User
Super User

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.

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
Super User
Super User

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
Super User
Super User

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

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...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors