Formula using nearest time before a calculated value?

[Betsy]

Hi there!

I have question and response data from students for which I use ID numbers (or lack thereof) as information, specifically to calculate the number of times they asked for our service to stop. I'm trying to see if specific questions lead to subsequent stop outs.

The relevant data tables/columns look something like this:

A. Students

Student ID; Active

1; TRUE

2; FALSE

3; TRUE

B. Questions

Student ID; Question ID; Question Date

1; 200; 9/8/16

1; 201; 9/9/16

2; 200;  9/8/16

3; 200; 9/8/16

3; 201; 9/10/16

C. Responses

Student ID; Question ID; Text; Response Date; Question Date

1; 200; Yes; 9/8/16; 9/8/16

1; 201; 5; 9/9/16; 9/9/16

2; [blank]; Stop; 9/8/16

3; 201; 4; 9/11/16; 9/10/16

3; [blank]; get more sleep; 9/11/16

When students make a stop response to opt out of the system, there is no Question ID or Question Time directly associated with it. Students do make other random responses that also don't have Question IDs or Question Times associated with them. I calculate stops with a measure which counts when Question ID is 0 (I converted blank Question IDs to 0) from the Responses table and Active = FALSE from the Students table.

What I am trying to do is now tie stops to the previous question. So for whichever Question Date is closest to (on or before) a stop date (so a Response Date with a 0 Question ID for an inactive student), what is the Question ID?  In the above examples, Student 2s reponse would be counted as a stop, and the Question ID closest to that date would be 200. Student 3s reponse on 9/11/16 would not be a stop. Question Dates associated with Question IDs are not fixed. One student could get Question 200 on a different day than another student. On the other hand, students almost never receive more than one question on a given day.

Another way to say it: If student is stopped, what was last question ID? (this might be simpler).

I'm stumped as to how to set up a formula to give me these Question IDs that come closest prior to a stop calculcation. 

Thanks for any help and I'd be more than happy to clarify.

Betsy

[v-ljerr-msft · Accepted solution]

@Betsy

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

[Betsy]

@v-ljerr-msft @Greg_Deckler

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

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

[Greg_Deckler]

You should be able to do a COUNTROWS and then encase that in a CALCULATE with a filter condiation of [Is Stop] = "STOP"

[Betsy]

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.

[Betsy]

For anyone reading this: The solution above IS correct. I modelled it out with dummy data. In my real model, something is causing the Is Stop not to report/slice correctly, even when I perform the CALCULATE/FILTER as suggested above. May be something with my relationships model or some filter I've applied somewhere. 

[Greg_Deckler]

I had a something similarly strange go on with my Ohio schools model. I was getting a really bizarre result so I posted some sample data in the community. Someone built the model from my sample data, got the right answer. So I built a new model with the sample data and got the right answer. I then went back and rebuilt my model from scratch and it too worked. The original model though still doesn't work right and I have no answer as to why as I made sure that both my new and old models were identical in how they were configured with respect to relationships, etc. Weird.

[Greg_Deckler]

Haven't tried modeling this up, but I would expect that the answer would involve a:

MAX([Question ID])

[Betsy]

Sorry, I should have said the Question IDs are not treated as numbers, in the sense that they aren't sequential. They are created when the questions are put into the system, not when they are sent to a student. In my model I actually have all IDs set as data type text so I can sum them correctly. It could be that Question ID 200 comes after 350 for one student, and before 350 for another.

[Greg_Deckler]

No date/time stamp, just date? If date/time, use MAX([DateTime])

If they are sequentially entered into the database as a row as they are answered, you could add an ID column to your import query and then take the max of that.

If it is neither of those two cases, I don't see how you could accomplish what you are trying to do. There has to be some indication of it being "the last".