Forum Discussion
Formula using nearest time before a calculated value?
According to your description, you can use LOOKUPVALUE function to search Questions[Student ID], Questions[Question ID], Questions[Question Date] in Responses table, if the returned value is Blank and Active of student = FALSE, then the corresponding Question ID is the last question ID when the student is stopped. See my sample below.
I assume you have tables like below.
Students
Questions
Responses
1. Use the formula below to create a calculate column called "Is Stop" in Questions table to check if the Question ID is the last Question ID when the student is stopped.
Is Stop = IF ( ISBLANK ( LOOKUPVALUE ( Responses[Student ID], Responses[Student ID], Questions[Student ID], Responses[Question ID], Questions[Question ID], Responses[Question Date], Questions[Question Date] ) ) && RELATED ( Students[Active] ) = FALSE (), "Stop" )2. Then you should be able to show these Question IDs in report with a Slicer of column "Is Stop" like below.
Regards
According to your description, you can use LOOKUPVALUE function to search Questions[Student ID], Questions[Question ID], Questions[Question Date] in Responses table, if the returned value is Blank and Active of student = FALSE, then the corresponding Question ID is the last question ID when the student is stopped. See my sample below.
I assume you have tables like below.
Students
Questions
Responses
1. Use the formula below to create a calculate column called "Is Stop" in Questions table to check if the Question ID is the last Question ID when the student is stopped.
Is Stop =
IF (
ISBLANK (
LOOKUPVALUE (
Responses[Student ID],
Responses[Student ID], Questions[Student ID],
Responses[Question ID], Questions[Question ID],
Responses[Question Date], Questions[Question Date]
)
)
&& RELATED ( Students[Active] ) = FALSE (),
"Stop"
)2. Then you should be able to show these Question IDs in report with a Slicer of column "Is Stop" like below.
Regards
The IS STOP table is working. Thank you! It turns out (I believe) that a bunch of students are sending random responses without Question IDs before they stop (the stop out total I get by distinct student ID count in the report table is greater than my stop out calulcation I described above). So I think somehow I have to integrate MAX date or something that will report only the last question date/question ID before the stop, not all prior Question date/question IDs before a blank question ID .
In the modelling above, I think if Student 3 sent "get more sleep" before subsequently sending "stop" and then being deactivated, both "get more sleep" and "stop" is being counted.
Is this possible?
Thanks again,
Betsy
Actually, this is what's happening (sorry, with there is a large volumn of data so it was hard to tell at first):
Anyone who is stopped (so has a blank Question ID and is active=FALSE), all of their Question IDs to which they responded are being shown in the report, not just the blank ones. So if they responded 5 times before they stopped, they are counted under 5 question IDs.
You should be able to do a COUNTROWS and then encase that in a CALCULATE with a filter condiation of [Is Stop] = "STOP"
That's giving me the same count as the previous report table with the Stop slicer clicked, most of the time. Sometimes the count is higher.
