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 previ...
  • 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]
)
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