Forum Discussion

Betsy's avatar
Betsy
Helper IV
9 years ago
Solved

Formula using nearest time before a calculated value?

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 ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

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

 

MAX([Question ID])
Betsy's avatar
Betsy
Helper IV
9 years ago

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's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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