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.
Hi all,
I have a table which looks like this:
container_nr | Status |
OOLU3874481 | completed |
OOLU3874481 | pending |
OOLU3874481 | rejected |
OOLU3333333 | pending |
I am looking for a formula for the following. If there are multiple rows with the same 'container_nr', and the 'Status' of one of these rows is 'completed', I want a 'yes' in an extra column:
Hope someone can help me with this?!
regards,
John
container_nr | Status | completed? |
OOLU3874481 | completed | yes |
OOLU3874481 | pending | yes |
OOLU3874481 | rejected | yes |
OOLU3333333 | pending | no |
Solved! Go to Solution.
Hi,
Try this calculated column formula
=IF(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[container_nr]=EARLIER(Data[container_nr])&&Data[Status]="Completed"))>=1,"Yes","No")
Hope this helps.
Are there multiple tables involved here? The example I put together was for everything in a single table and it seemed like it was returning the correct status.
good day Greg,
here is what i did: I took a beer, had a good rest, tried it again and found out it is woking fine.
Thank you so much!
john
Hi,
Try this calculated column formula
=IF(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[container_nr]=EARLIER(Data[container_nr])&&Data[Status]="Completed"))>=1,"Yes","No")
Hope this helps.
good day Ashish,
here is what i did: I took a beer, had a good rest, tried it again and found out it is woking fine.
Thank you so much!
john
You are welcome.
You want EARLIER:
completed = VAR __table = FILTER(ALL('Table14'),[container_nr] = EARLIER([container_nr]) && [Status] = "completed") RETURN IF(COUNTX(__table,[Status])>=1,"yes","no")
See table 14 of attached.
Also, here is a good article on EARLIER:
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Hi Greg,
I am almost there, but one thing is not correct.
your formula gives me also a 'yes' for the OOLU3890311.
This is not correct, as this container_nr has the status PENDING.
Can you please advise?
completed? =
VAR __table =
FILTER (
ALL ( OOCL_RTM_Depotsmart_PTI );
OOCL_RTM_Depotsmart_PTI[Container_nr] = EARLIER ( [container_nr] )
&& [Status] = "completed"
)
RETURN
IF ( COUNTX ( OOCL_RTM_Depotsmart_PTI; [Status] ) >= 1; "yes"; "no" )
container_nr | Container_nr | Status | completed? |
OOLU3874481 | OOLU3874481 | completed | yes |
OOLU3874481 | OOLU3874481 | pending | yes |
OOLU3874481 | OOLU3874481 | rejected | yes |
OOLU3890311 | OOLU3890311 | pending | yes |
Are there multiple tables involved here? The example I put together was for everything in a single table and it seemed like it was returning the correct status.
good day Greg,
here is what i did: I took a beer, had a good rest, tried it again and found out it is woking fine.
Thank you so much!
john
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |