We have a primary table used to track student information (โStudents1โ). We also have a connected table that stores related assessment and outcome records (โAssessmentsโ). Each record in the โStudents1โ table will have multiple related records from โAssessmentsโ that appear in the Students1 table in a list column.
I am able to successfully create an expression that checks whether a particular value is present in any of the related assessment records for a particular student. For example:
Contains([Related Assessments][Pre or Post], โpostโ)
If I understand correctly, this expression will check to see whether any of the studentโs Related "Assessments records contains โpostโ in the [Pre or Post] column. Basically, does the student have a posttest.
Now, I want to create an expression that checks whether any one of the related rows has both a value of โpostโ in the [Pre or Post] column, and does not contain a โ+โ in the [Score] column. Does anyone have any advice on how to do this? Or maybe a resource you could link to?
The best I can come up with is:
AND(
Contains([Related Assessments][Pre or Post], โpostโ),
NOT(Contains([Related Assessments][Score], โ+โ))
)
However, I donโt think this works. For a particular student record, one related row might have โpostโ in [Pre or Post], and a second related row might not have โ+โ in the [Pre or Post] column. In that case, the expression would evaluate to true, right? But I want it to only evaluate to true if both of these conditions are true for a single row/record.
Thanks for reading, and for any advice you might have.
Solved! Go to Solution.
Easy enough, though itโs not clear exactly what you mean by this:
Would the [Score]
value be exactly +
? Or would the value possibly contain other characters, like +75
? Is [Score]
a numeric type column, like Number
or Decimal
? Or is it a textual type, like Text
?
If [Score]
would be exactly +
, try this:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
("+" = [Score])
)
)
)
If [Score]
is a numeric type column, and youโre looking for positive scores, try this instead:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
([Score] > 0.0)
)
)
)
If [Score]
is a textual column, and the +
would only occur as the first character if present, try this:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
STARTSWITH([Score], "+")
)
)
)
If [Score]
is a textual column, and the +
might occur amid other characters, try this:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
CONTAINS([Score], "+")
)
)
)
What do you want exactly?
Hello, and thank you very much for answering. I think I want a yes/no answer, since Iโm trying to count the number of students that have an assessment that meets both conditions. The expression is part of a larger expression, which I will share below in case it helps clarify. The ultimate goal is to generate a count of all students who have at least one assessment that meets both conditions. The students must have also registered between 07/01/21 and 11/11/2021 OR have registered before 07/01/21 and have attendance after 07/01/21.
COUNT(
FILTER(
โStudents1โ,
AND((Contains([Related Assessments][Pre or Post], โpostโ)),
OR(
AND(
DATETIME([Related SignUpSheets][Timestamp])>=โ07/01/2021โ,
DATETIME([Related SignUpSheets][Timestamp])<=โ11/11/2021โ
),
AND(
DATETIME([Related SignUpSheets][Timestamp])<โ07/01/2021โ,
DATETIME([Last Attendance])>= โ07/01/2021โ
)
)
)))
You may want to make it more simple:
ISNOTBLANK(
FILTER(โStudents1โ,
AND(
Contains([Related Assessments][Pre or Post], โpostโ),
OR(
AND(
ANY([Related SignUpSheets][Timestamp])>=โ07/01/2021โ,
ANY([Related SignUpSheets][Timestamp])<=โ11/11/2021โ
),
AND(
ANY([Related SignUpSheets][Timestamp])<โ07/01/2021โ,
[Last Attendance]>= โ07/01/2021โ
)
)
)
)
)
I removed Datetime()
as TimeStamp are supposed to be DateTime type.
I added ANY instead, as [Related SignUpSheets][Timestamp] will give a list of TimeStamp, and you probably want just one: I guess this is by construction, but you are supposed to have only one SignUpSheet per student ?
Also:
How can I create an expression that checks whether there is at least one related row that meets multiple conditions?
For this purpose, I replaced COUNT that would require a comparison with zero, with ISNOTBLANK() which will give a Yes/No output.
For reference:
Small tip: for displaying indented expression, you can wrap it with this:
Hi Aurelien,
Thank you very much for the feedback, and all of the helpful links.
Each student will have one SignUpSheet.
To clarify, the goal of the full expression in my last post is to produce a number as an output, namely the total number of fall students with a valid posttest. In this case, COUNT() is what is needed, isnโt it?
I am having issues specifically with this part of the expression:
Contains([Related Assessments][Pre or Post], โpostโ)
This part checks to see if the assessment is a post test, which seems to work fine. However, I want to expand it so it checks whether there is at least one related assessment that both has โpostโ in the [Pre or Post] column and that does not contain โ+โ in the [Score] column. The โ+โ indicates the test is invalid, so I donโt want to include rows with that value.
Since [Related Assessments] produces a list, I donโt think I can simply add another Contains() expression. The reason being I might have one list item that meets condition A and another that meets condition B. What I really want to check is if there is an item in the list that meets both conditions. In other words, when I pull the list of related assessments, I want to only include the rows that both are post tests and do not contain โ+โ in the score column.
Thank you,
I would do it a little bit different:
COUNT(
UNIQUE(
SELECT(
Assessments[RefToStudentsColumn], /*Change it with the real names*/
AND(
[Pre or Post]="Post",
[Score]="+",
OR(
AND(
DATE([Timestamp])>=DATE("07/01/2021"),
DATE([Timestamp])<=DATE("11/11/2021")
),
AND(
DATE([Timestamp])<DATE("07/01/2021"),
DATE([RefToStudentsColumn].[Last Attendance])>=DATE("07/01/2021")
)
)
)
)
)
)
BTW
Containts()
is for strings/text. Like if โJustinโ contains โuโ.
In()
is for lists of anything. Like if 43 is in the list โ21, 32, 43โ
Easy enough, though itโs not clear exactly what you mean by this:
Would the [Score]
value be exactly +
? Or would the value possibly contain other characters, like +75
? Is [Score]
a numeric type column, like Number
or Decimal
? Or is it a textual type, like Text
?
If [Score]
would be exactly +
, try this:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
("+" = [Score])
)
)
)
If [Score]
is a numeric type column, and youโre looking for positive scores, try this instead:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
([Score] > 0.0)
)
)
)
If [Score]
is a textual column, and the +
would only occur as the first character if present, try this:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
STARTSWITH([Score], "+")
)
)
)
If [Score]
is a textual column, and the +
might occur amid other characters, try this:
ISNOTBLANK(
SELECT(
[Related Assessments][_ROWNUMBER],
AND(
("post" = [Pre or Post]),
CONTAINS([Score], "+")
)
)
)
A huge thank you to everyone. Youโve all been very helpful.
[Score] is a textual column that might have a + amid other characters. I used the expression Steve provided for that situation, and it seemed to work for our purposes. Hurray!
I believe the formula SkrOYC would have achieved the same result. Itโs interesting to see different methods for achieving the same goal, and seeing those methods helps me understand the underlying logic a bit better as well.
COUNT(
UNIQUE(
SELECT(
Assessments[RefToStudentsColumn], /*Change it with the real names*/
AND(
[Pre or Post]="Post",
CONTAINS([Score], "+"), /*Good example of usecase for Contains()*/
OR(
AND(
DATE([Timestamp])>=DATE("07/01/2021"),
DATE([Timestamp])<=DATE("11/11/2021")
),
AND(
DATE([Timestamp])<DATE("07/01/2021"),
DATE([RefToStudentsColumn].[Last Attendance])>=DATE("07/01/2021")
)
)
)
)
)
)
User | Count |
---|---|
41 | |
36 | |
28 | |
23 | |
16 |