Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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?
do not hesitate to give a kudo to useful posts and mark solutions as solution
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.
There is no such option in the Community
do not hesitate to give a kudo to useful posts and mark solutions as solution
Very strange.
To help you visualize my problem: I only have an x everywhere.
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
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
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.
Are you sure you can't do this as well?
try a New Measure, not column. it should work 🙂
do not hesitate to give a kudo to useful posts and mark solutions as solution
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 😕
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
Thank you so much - you did a great job 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |