cancel
Showing results for
Did you mean:
Regular Visitor

## Find first occurence of value

Hello dear PowerBI-Community

What I find easy in excel, I don't know how to solve it in PowerBI.

But I have to process the data directly in PowerBI so my question is:

In PowerBI I have the columns transportID and transportIdOccurence as you can see below.

The gray column is the one that needs to be solved.

For this purpose the following formula can be used in excel: =WENN(A2<>A1;"1";"x")

Any ideas?

 transportId transportIdOccurence checkIfFirstTime 159 1 1 567 2 1 567 2 x 890 3 1 890 3 x 890 3 x 789 5 1 789 5 x 456 5 1 789 5 1 456 5 1 456 5 x 456 5 x 456 5 x 789 5 1 789 5 x 345 6 1 345 6 x 345 6 x 345 6 x 345 6 x 345 6 x 123 7 1 123 7 x 123 7 x 123 7 x 123 7 x 123 7 x 123 7 x

Thank you so much for your support!

Cheers

qwertzuiop

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Find first occurence of value

You will need to add an Index to your table in Power Query. Then you can do something like:

``````CheckFirstTime =
VAR __id = [Index]
VAR __Table = FILTER(ALL('Table'),[transportId] = EARLIER([transportId]))
VAR __min = MINX(__Table,[Index])
RETURN
IF(__id = __min, TRUE(), FALSE())``````

See attached.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

5 REPLIES 5
Super User

## Re: Find first occurence of value

You will need to add an Index to your table in Power Query. Then you can do something like:

``````CheckFirstTime =
VAR __id = [Index]
VAR __Table = FILTER(ALL('Table'),[transportId] = EARLIER([transportId]))
VAR __min = MINX(__Table,[Index])
RETURN
IF(__id = __min, TRUE(), FALSE())``````

See attached.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

## Re: Find first occurence of value

Thank you so much @Greg_Deckler for your excellent and fast help.

It all worked out fine and I'm more than happy.

Many thanks also for your way of working with the attachment, this made it easy for me to adapt the solution

Following on from your solution, I would like to ask you another question.

As you can see, i added the column created, timeOld and timeNew to the table

Again there is a gray column whose values are to be calculated.

What does this column (Change/NoChange) say?

it checks a transport if the first timeOld shown is not the same as the last timeNwe. In this case it would be a change.

If this rule is taken into account, it is clear for a transport with one occurrence "change" is always written.

Transports with several occurrences must be compared with each other (highlighted in color).

Do you have any idea how to solve this in PowerBI?

I would be very grateful for your support.

 transportId created timeOld timeNew transportIdOccurence checkIfFirstTime Change/NoChange 159 2019-10-23T04:15:20 2019-10-23T07:30 2019-10-23T15:30 1 True Change 567 2019-10-19T08:30:09 2019-10-22T12:30 2019-10-23T12:30 2 True Change 567 2019-10-19T09:45:09 2019-10-23T12:30 2019-10-21T12:30 2 False x 890 2019-09-28T19:00:11 2019-10-03T14:00 2019-10-01T14:00 3 True NoChange 890 2019-09-30T07:00:12 2019-10-01T14:00 2019-10-02T14:00 3 False x 890 2019-09-30T09:45:11 2019-10-02T14:00 2019-10-03T14:00 3 False x 345 2019-10-01T05:30:28 2019-10-01T12:30 2019-10-03T12:30 6 True Change 345 2019-10-01T15:30:16 2019-10-03T12:30 2019-10-02T12:30 6 False x 345 2019-10-01T17:15:21 2019-10-02T12:30 2019-10-03T12:30 6 False x 345 2019-10-01T17:30:21 2019-10-03T12:30 2019-10-02T12:30 6 False x 345 2019-10-02T06:30:10 2019-10-02T12:30 2019-10-03T12:30 6 False x 345 2019-10-02T08:15:19 2019-10-03T12:30 2019-10-03T08:25 6 False x 123 2019-10-29T11:45:12 2019-10-30T14:00 2019-11-04T11:30 7 True Change 123 2019-10-29T19:45:15 2019-11-04T11:30 2019-10-31T14:00 7 False x 123 2019-10-29T20:00:22 2019-10-31T14:00 2019-11-04T11:30 7 False x 123 2019-10-30T07:00:15 2019-11-04T11:30 2019-10-31T14:00 7 False x 123 2019-10-30T12:45:15 2019-10-31T14:00 2019-11-04T11:30 7 False x 123 2019-10-31T06:30:10 2019-11-04T11:30 2019-10-31T14:00 7 False x 123 2019-11-01T08:00:18 2019-10-31T14:00 2019-11-01T14:00 7 False x

Cheers

qwertzuiop

Super User

## Re: Find first occurence of value

First, thanks back for the excellent formatting of the question and including sample data that is easy to cut and paste! That way I didn't need to refer you to my article, How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Your Change/NoChange column is similar. It should go something like this:

``````CheckFirstTime =
IF(
[checkIfFirstTime],
VAR __id = [Index]
VAR __Table = FILTER(ALL('Table'),[transportId] = EARLIER([transportId]))
VAR __min = MINX(__Table,[Index])
VAR __max = MAXX(__Table,[Index])
VAR __minValue = MAXX(FILTER(__Table,[Index] = __min),[timeOld])
VAR __maxValue = MAXX(FILTER(__Table,[Index] = __max),[timeNew])
RETURN
IF(__minValue <> __maxValue, "Change", "NoChange"),
"x"``````

Again, attached revised PBIX.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

## Re: Find first occurence of value

Dear @Greg_Deckler

It gives me great pleasure to work with you.

Your solutions are quick and easy to implement.

You are an absolutely legitimate super user and can recommend yourself for any topics and users

Following on from this and looking ahead, I would like to ask whether I may ask you further questions in this thread, should I have another concern in this direction?

Have a wonderful day!

Cheers

qwertzuiop

Highlighted
Regular Visitor

## Re: Find first occurence of value

Hello @Greg_Deckler

Thank you again for your help so far.

I have a guilty conscience from time to time, but you are currently my only point of contact who answers my questions quickly, easily and very well. So I allow myself to contact you again.

As you can see below I have extended the table with the gray column called EarlySameLate.

I would like to be able to make a statement as to whether a transport is finally (last timeNew of the transport) earlier, later or at the same time as originally (first timeOld of the transport).

That means a "NoChange" is always "Same". A "Change" can be "Early" or "Late".

Do you have the time, desire and an idea how the solution can be found?

I'd be so grateful.

Cheers

qwertzuiop

 transportId created timeOld timeNew transportIdOccurence checkIfFirstTime ChangeNoChange EarlySameLate 890 2019-09-28T19:00:11 2019-10-03T14:00 2019-10-01T14:00 3 True NoChange Same 890 2019-09-30T07:00:12 2019-10-01T14:00 2019-10-02T14:00 3 False x x 890 2019-09-30T09:45:11 2019-10-02T14:00 2019-10-03T14:00 3 False x x 345 2019-10-01T05:30:28 2019-10-01T12:30 2019-10-03T12:30 6 True Change Late 345 2019-10-01T15:30:16 2019-10-03T12:30 2019-10-02T12:30 6 False x x 345 2019-10-01T17:15:21 2019-10-02T12:30 2019-10-03T12:30 6 False x x 345 2019-10-01T17:30:21 2019-10-03T12:30 2019-10-02T12:30 6 False x x 345 2019-10-02T06:30:10 2019-10-02T12:30 2019-10-03T12:30 6 False x x 345 2019-10-02T08:15:19 2019-10-03T12:30 2019-10-03T08:25 6 False x x 567 2019-10-19T08:30:09 2019-10-22T12:30 2019-10-23T12:30 2 True Change Early 567 2019-10-19T09:45:09 2019-10-23T12:30 2019-10-21T12:30 2 False x x 159 2019-10-23T04:15:20 2019-10-23T07:30 2019-10-23T15:30 1 True Change Late 123 2019-10-29T11:45:12 2019-10-30T14:00 2019-11-04T11:30 7 True Change Late 123 2019-10-29T19:45:15 2019-11-04T11:30 2019-10-31T14:00 7 False x x 123 2019-10-29T20:00:22 2019-10-31T14:00 2019-11-04T11:30 7 False x x 123 2019-10-30T07:00:15 2019-11-04T11:30 2019-10-31T14:00 7 False x x 123 2019-10-30T12:45:15 2019-10-31T14:00 2019-11-04T11:30 7 False x x 123 2019-10-31T06:30:10 2019-11-04T11:30 2019-10-31T14:00 7 False x x 123 2019-11-01T08:00:18 2019-10-31T14:00 2019-11-01T14:00 7 False x x

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,375)