Find first occurence of value

[qwertzuiop]

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

[Greg_Deckler · Accepted solution]

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.

[qwertzuiop]

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

[Greg_Deckler]

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.

[qwertzuiop]

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

[qwertzuiop]

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