cancel
Showing results 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

Regular Visitor

## Measure to determine a text value using the value of 2 other measures and filtered by candidate type

I need to determine if a student requires an Individual Improvement Plan (IDP) based on their exam score for a particular portion of an exam and based on the type of Candidate (i.e. RO or SRO).  The RO candidate just has one exam score, while the SRO has 3 of which only 2 of them are factored into the IDP requirement.   The exams have 2 sections: Section 1 - RO, Section 2 - SRO ONLY.  For the SRO  candidates, the required scores are : SRO ONLY and Overall (RO and SRO ONLY sections).  The RO section score for the SRO candidate is tracked for information ONLY.

Requirements for an IDP:

1) Exam Score < 85% (NOTE: I used 86% in the formulas below in order to test the measure against real data.)

2) Exam Score < 90% AND < 5% below the class average (NOTE: For the RO candidates the class average only includes the RO scores and not the SRO scores.)

3) For SRO Section, >= 10 SRO ONLY questions on the exam

I have been able to get the first part of the RO IDP measure to determine if an IDP is required or not, however no matter what the results include the SRO candidates.  Here is what one of the formula variations that I have tried thus far.

RO Determine IF IDP Required =
CALCULATE(
IF(
[Average for RO] < .86
||
[Average for RO] < .9 && [Average for RO] < [Exam Average - RO] - 5,
"IDP Required",
"NO IDP Required"
),
FILTER(RELATEDTABLE('ILT 21-1 Candidate Data'), 'ILT 21-1 Candidate Data'[CandidateType] in {"RO"})
)

Below are the two reference measures in the above formula:

Average for RO = CALCULATE(AVERAGEX('ILT 21-1 Results Grid', [# RO Points for ROs] / [Actual Number of RO Questions]), FILTER(SUMMARIZE('ILT 21-1 Results Grid', 'ILT 21-1 Results Grid'[Exam#], 'ILT 21-1 Results Grid'[VisionExam#], 'ILT 21-1 Candidate Data'[CandidateType]), 'ILT 21-1 Candidate Data'[CandidateType] = "RO"))

Exam Average - RO = CALCULATE(DIVIDE([# RO Points for ROs], [Actual Number of RO Questions] * [Total # of RO Candidates]), FILTER(SUMMARIZE('ILT 21-1 Results Grid', 'ILT 21-1 Candidate Data'[CandidateType], 'ILT 21-1 Results Grid'[Exam#], 'ILT 21-1 Results Grid'[VisionExam#]), 'ILT 21-1 Candidate Data'[CandidateType] = "RO"), ALL('ILT 21-1 Results Grid'[StudentName]), ALL('ILT 21-1 Results Grid'[StudentName]))

Picture of the applicable portion of my data model:

Data Model

I am sure I am making somekind of newbie mistake with row context versus filter context, but after numerous hours of searching the blogs, reading various articles, and trial and error I have yet to figure it out.  Any assistance to develop these measures would be greatly appreciated.

2 REPLIES 2
Super User

Altough you have some detailed explanation without any data is difficult to give you the correct answer.

If the information is sensitive please share it trough private message.

An expected result based on your data would be great if possible.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

The website won't let me send this to you via the private message as it said I exceeded the number of private messages so I am trying to post the information you requested.

I wanted to use the measures for two different visualizations.

1. A matrix with the rows with the following hierarchy: Candidate Type, Student Name. The columns being the Exam# and ExamType (RO Score, SRO ONLY Score, SRO Overall) (NOTE: For the RO candidate type has only the RO Score as they do not take the SRO portion.) The data would be the actual scores the each exam/exam section. I was also wanting to use the measure to apply conditional formating to change the background color (usually a light red/purple color) if the candidate exam meets the given criteria for an IDP.
2. The primary need is a visual or visuals that dispays the following: lists the number of IDPs for each candidate type for each exam and exam section i.e. Row 1: RO 1 IDP Required (NOTE: RO candidates ONLY), Row 2: SRO ONLY 2 IDP Required, Row 3: SRO OVERALL 3 IDP Required. The exam Title/exam # can be in the row or column header. list the students that require an IDP for that exam broken down by the exam section and candidate type.

Thanks for asking as your assistance is very much appreciated as I am trying to learn and figure this out. Robert