Forum Discussion

CHJustesen's avatar
CHJustesen
New Member
8 years ago
Solved

Finding the previos occurrence within same table based on one criteria

Hi guys,

 

I have a list of time registers from users registering that they have been spending time on a customer case, as well as if they have held a break. I would like to find the user who previously registered time on the same case. My data and my goal looks like this:

 

Entry No    User     Case        OUTCOME

100            V1        492524    

101            V3        502392    

102            V3        492524    V1

103            V4        492524    V3

104            V1        BREAK

105            V3        492524    V4

 

I have tried with the calculate and the lookupvalue function without any luck.

 

Hope that someone can be of help! :-)

 

Best regards

Claus

  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    CHJustesen,

     

    You may add a calculated column as shown below.

    Column =
MAXX (
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Case] = EARLIER ( Table1[Case] )
                && Table1[Entry No] < EARLIER ( Table1[Entry No] )
        ),
        Table1[Entry No], DESC
    ),
    Table1[User]
)

5 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    CHJustesen,

     

    You may add a calculated column as shown below.

    Column =
MAXX (
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Case] = EARLIER ( Table1[Case] )
                && Table1[Entry No] < EARLIER ( Table1[Entry No] )
        ),
        Table1[Entry No], DESC
    ),
    Table1[User]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Is there a way to find it out in the measure?
      Without the calculated collumn?

      In my example the table is based upon the table/matrix visualation, that is gathered based upon relevant collumns from other tables and measures such as:

      MarginYTD = TOTALYTD([Margin Real+];'Calendar'[Date])

      Table has a collumn with;
      Company name     Margin-Year  -Q      -Month     -DAY

      4.PNG 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-chuncz-msft

       

      I hope this is not too much to ask, as the post is solved, but...

       

      Could you please briefly clarify my doubts? I have notes in between on what I think it is.

       

         - Why the use of MAXX and TOPN together?

               - I suppose it is just to get one value out of the calculation, right? The maximum (newest) Entry No and the only user of that TOP 1?

         - What is the EARLIER function purpose and why is it used here?

               - I'm really clueless about this one. Is it somehow related with date intelligence?

       

      Thanks in advance.

       

      Cheers!

    • jimbob2285's avatar
      jimbob2285
      Advocate IV
      3 years ago

      Hi

       

      I've used this DAX, which is giving me a value from the earlier record, but I need a value from the later record, although I can't figure out how to adapt the DAX, there's no Later alternative to Earlier

       

      Can you help please

       

      Cheers

      Jim

  • CHJustesen's avatar
    CHJustesen
    New Member
    8 years ago

    Hi guys,

     

    I have a table with a lot of time registers from all of our users registering time on customer-cases and when they are having breaks. If I have more than one time registration on the same case, I would like to find the user who previously registered time on the same case. I want to exclude the break time registrations as they are not needed. All the data is in the same table which could look like this:

     

    Entry No   User      Case No     OUTCOME

    100           V1         495723

    101           V5         502312

    102           V5         592932

    103           V3         495723        V1

    104           V4         495723        V3

    105           V1         BREAK

     

    I have tried with the calculate function with filters as well as the lookupvalue function but with no luck for me.

     

    Hope that somebody can help me! :-)