cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qwertzuiop Regular Visitor
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?

 

transportIdtransportIdOccurencecheckIfFirstTime
15911
56721
5672x
89031
8903x
8903x
78951
7895x
45651
78951
45651
4565x
4565x
4565x
78951
7895x
34561
3456x
3456x
3456x
3456x
3456x
12371
1237x
1237x
1237x
1237x
1237x
1237x

 

Thank you so much for your support!

Cheers 

qwertzuiop

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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!

View solution in original post

5 REPLIES 5
Super User
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!

View solution in original post

qwertzuiop Regular Visitor
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.

 

transportIdcreatedtimeOldtimeNewtransportIdOccurencecheckIfFirstTimeChange/NoChange
1592019-10-23T04:15:202019-10-23T07:302019-10-23T15:301TrueChange
5672019-10-19T08:30:092019-10-22T12:302019-10-23T12:302TrueChange
5672019-10-19T09:45:092019-10-23T12:302019-10-21T12:302Falsex
8902019-09-28T19:00:112019-10-03T14:002019-10-01T14:003TrueNoChange
8902019-09-30T07:00:122019-10-01T14:002019-10-02T14:003Falsex
8902019-09-30T09:45:112019-10-02T14:002019-10-03T14:003Falsex
3452019-10-01T05:30:282019-10-01T12:302019-10-03T12:306TrueChange
3452019-10-01T15:30:162019-10-03T12:302019-10-02T12:306Falsex
3452019-10-01T17:15:212019-10-02T12:302019-10-03T12:306Falsex
3452019-10-01T17:30:212019-10-03T12:302019-10-02T12:306Falsex
3452019-10-02T06:30:102019-10-02T12:302019-10-03T12:306Falsex
3452019-10-02T08:15:192019-10-03T12:302019-10-03T08:256Falsex
1232019-10-29T11:45:122019-10-30T14:002019-11-04T11:307TrueChange
1232019-10-29T19:45:152019-11-04T11:302019-10-31T14:007Falsex
1232019-10-29T20:00:222019-10-31T14:002019-11-04T11:307Falsex
1232019-10-30T07:00:152019-11-04T11:302019-10-31T14:007Falsex
1232019-10-30T12:45:152019-10-31T14:002019-11-04T11:307Falsex
1232019-10-31T06:30:102019-11-04T11:302019-10-31T14:007Falsex
1232019-11-01T08:00:182019-10-31T14:002019-11-01T14:007Falsex

 

Cheers

qwertzuiop

 

Super User
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!

Highlighted
qwertzuiop Regular Visitor
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

qwertzuiop Regular Visitor
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

 

transportIdcreatedtimeOldtimeNewtransportIdOccurencecheckIfFirstTimeChangeNoChangeEarlySameLate
8902019-09-28T19:00:112019-10-03T14:002019-10-01T14:003TrueNoChangeSame
8902019-09-30T07:00:122019-10-01T14:002019-10-02T14:003Falsexx
8902019-09-30T09:45:112019-10-02T14:002019-10-03T14:003Falsexx
3452019-10-01T05:30:282019-10-01T12:302019-10-03T12:306TrueChangeLate
3452019-10-01T15:30:162019-10-03T12:302019-10-02T12:306Falsexx
3452019-10-01T17:15:212019-10-02T12:302019-10-03T12:306Falsexx
3452019-10-01T17:30:212019-10-03T12:302019-10-02T12:306Falsexx
3452019-10-02T06:30:102019-10-02T12:302019-10-03T12:306Falsexx
3452019-10-02T08:15:192019-10-03T12:302019-10-03T08:256Falsexx
5672019-10-19T08:30:092019-10-22T12:302019-10-23T12:302TrueChangeEarly
5672019-10-19T09:45:092019-10-23T12:302019-10-21T12:302Falsexx
1592019-10-23T04:15:202019-10-23T07:302019-10-23T15:301TrueChangeLate
1232019-10-29T11:45:122019-10-30T14:002019-11-04T11:307TrueChangeLate
1232019-10-29T19:45:152019-11-04T11:302019-10-31T14:007Falsexx
1232019-10-29T20:00:222019-10-31T14:002019-11-04T11:307Falsexx
1232019-10-30T07:00:152019-11-04T11:302019-10-31T14:007Falsexx
1232019-10-30T12:45:152019-10-31T14:002019-11-04T11:307Falsexx
1232019-10-31T06:30:102019-11-04T11:302019-10-31T14:007Falsexx
1232019-11-01T08:00:182019-10-31T14:002019-11-01T14:007Falsexx

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,657)