cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Finding the previos occurrence within same table based on one criteria

@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]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
New Member

Finding the previous occurrence of a text based on two criterias within same table

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! 🙂

Highlighted
Community Support
Community Support

Re: Finding the previos occurrence within same table based on one criteria

@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]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Finding the previos occurrence within same table based on one criteria

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 

Highlighted
Responsive Resident
Responsive Resident

Re: Finding the previos occurrence within same table based on one criteria

@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!

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors