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

Betsy's avatar
Betsy
Helper IV
9 years ago

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's avatar
Betsy
Helper IV
9 years ago

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

    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.