cancel
Showing results for
Did you mean:
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

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

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.
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

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

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.
Highlighted
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

Highlighted
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?

Cheers!

Announcements

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our 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

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

Top Solution Authors
Top Kudoed Authors