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

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

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.