Check first and lasttime of ID-Occurence

[qwertzuiop]

Hello Dear PowerBI Community

I would be very grateful, if you could help me.

As can be seen in the table, there are different transports with IDs.

I would like to check chronologically which message per transport ID is sent first and which last.

The result should be in a calculated table as in the example.

transportID	created	CheckOccurence
123	22.10.2019 01:45	FirstTime
123	23.10.2019 03:15	x
123	24.10.2019 21:45	x
456	24.10.2019 22:11	FirstTime
456	24.10.2019 23:32	LastTime
789	24.10.2019 23:50	FirstTime
789	25.10.2019 03:30	x
789	25.10.2019 05:20	LastTime
123	25.10.2019 16:30	x
123	25.10.2019 19:12	LastTime

Cheers!

qwertzuiop

[az38]

Hi @qwertzuiop 

try a measure

CheckOccurence = 
var _first = calculate(MIN(Table1[created]);ALLEXCEPT(Table1;Table1[transportID]))
var _last = calculate(MAX(Table1[created]);ALLEXCEPT(Table1;Table1[transportID]))
var _trID = SELECTEDVALUE(Table1[transportID])

RETURN
SWITCH(TRUE();
SELECTEDVALUE(Table1[created]) = _first && SELECTEDVALUE(Table1[transportID]) =_trID; "FirstTime";
SELECTEDVALUE(Table1[created]) = _last && SELECTEDVALUE(Table1[transportID]) =_trID; "LastTime";
"x"
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[qwertzuiop]

thanks a lot.

Unfortunatelly it doesn't work.

Could you please send your work in a powerBI-File so I can check and unterstand it?

[az38]

@qwertzuiop 

https://ufile.io/z0rsrmnw 

do not hesitate to give a kudo to useful posts and mark solutions as solution

[qwertzuiop]

Thank you so much for your fast answers.

Couls you upload the file directly in here.

I'm in a secure network which not allows to visit your url.

[az38]

@qwertzuiop 

There is no such option in the Community

do not hesitate to give a kudo to useful posts and mark solutions as solution

[qwertzuiop]

Very strange.

To help you visualize my problem: I only have an x everywhere.

[az38]

@qwertzuiop 

I got it. YOu create a column, but need a measure. try measure

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

@qwertzuiop 

it could be problem with delimiter. try use "," instead of my ";"

do not hesitate to give a kudo to useful posts and mark solutions as solution

[qwertzuiop]

Does not work when changing ; to ,

https://community.powerbi.com/t5/Desktop/Find-first-occurence-of-value/m-p/850429#M408093

please check this post.

There my hero uploaded a file called working56.pbix as solution.

Are you sure you can't do this as well?

[az38]

@qwertzuiop 

try a New Measure, not column. it should work 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution

[qwertzuiop]

Thank you very much @az38 

Now i could solve the problem and it works.

But is there a way for a calculated column too?

Would be better for me 😕

[az38 · Accepted solution]

@qwertzuiop 

no problem, it should work as column. set as solution if it will help

Column = 
var _first = calculate(MIN(Table1[created]);ALLEXCEPT(Table1;Table1[transportID]))
var _last = calculate(MAX(Table1[created]);ALLEXCEPT(Table1;Table1[transportID]))
var _trID = [transportID]

RETURN
SWITCH(TRUE();
[created] = _first && [transportID] =_trID; "FirstTime";
[created] = _last && [transportID] =_trID; "LastTime";
"x"
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[qwertzuiop]

Thank you so much - you did a great job 🙂