Show/Count highest ID if

[Roym]

I have an Excel table like the one show below. Per name you have different ID's, Status, and Month. In my PowerBI table I need to show/count the highest ID, but only if the previous one is either Effective or Ineffective. So in the below example the highest ID that is not Effective or Ineffective is ID#2 from February as it is Overdue. So that one should be shows/counted in my table. Hopefully someone can help me a bit in the right direction that I need to look for to achieve this.

ID	Name	Status	Month
4	Issue1	Open	July
3	Issue1	Open	March
2	Issue1	Overdue	February
1	Issue1	Effective	January

[ryan_mayu · Accepted solution]

@Roym

pls try this

Column = 
VAR _id=maxx(FILTER('Table',('Table'[Status]="Effectvie"||'Table'[Status]="Ineffective")&&'Table'[ID]<>CALCULATE(max('Table'[ID]),ALLEXCEPT('Table','Table'[Name]))),'Table'[ID])
return if('Table'[ID]=_id+1,"highest value")

[ryan_mayu · Accepted solution]

@Roym 

one thing want to be mentioned. if you have other issues, you need to modify the DAX

Column = 
VAR _id=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&('Table'[Status]="Effective"||'Table'[Status]="Ineffective")&&'Table'[ID]<>CALCULATE(max('Table'[ID]),ALLEXCEPT('Table','Table'[Name]))),'Table'[ID])
return if('Table'[ID]=_id+1,"highest value")

[ryan_mayu]

@Roym 

here is a workaround for you

Column = 
var staus=maxx(FILTER('Table (2)','Table (2)'[Name]=EARLIER('Table (2)'[Name])&&'Table (2)'[ID]=EARLIER('Table (2)'[ID])-1),'Table (2)'[Status])
return if(staus="Effective"||staus="Ineffective","Highest ID")

[Roym]

First of all, thanks for the help/effort!!! 

I tried it and with the data example as I posted, it works. But once I added some additional data it is not fully working. It shows two rows that match the criteria, but it should only show per issue one result. Is that possible to do?

[ryan_mayu · Accepted solution]

@Roym

pls try this

Column = 
VAR _id=maxx(FILTER('Table',('Table'[Status]="Effectvie"||'Table'[Status]="Ineffective")&&'Table'[ID]<>CALCULATE(max('Table'[ID]),ALLEXCEPT('Table','Table'[Name]))),'Table'[ID])
return if('Table'[ID]=_id+1,"highest value")

[Roym]

When working with the calculation I found out I have one question remaining. Hopefully someone can assist me with this last item. So the code from Ryan is working perfectly. Only I have one exception. If an issue (in the example below issue1) has ineffective, and then open/overdue it should mark the ineffective as highest value. What I tried is adjusting the code by removing '||'Table'[Status]="Ineffective"'.  This works for issue1, but then for issue2 in the example it doesn't give a highest value, they are all empty. Any idea how I can get this working?

Issue1	6	Overdue	Highest value
Issue1	5	Ineffective
Issue1	4	Effective
Issue2	3	Ineffective	Highest value
Issue2	2	Ineffective
Issue2	1	Ineffective

[ryan_mayu]

@Roym 

pls try this

Column = 
VAR _id=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&('Table'[Status]="Effective"||'Table'[Status]="Ineffective")&&'Table'[ID]<>CALCULATE(max('Table'[ID]),ALLEXCEPT('Table','Table'[Name]))),'Table'[ID])
VAR _staus=maxx(FILTER('Table','Table'[ID]=_id),'Table'[Status])
return if((_staus="Ineffective"&&'Table'[ID]=_id)||(_staus="Effective"&&'Table'[ID]=_id+1),"Highest ID")

[Roym]

thanks again for the support! I tried it and it not fully working correctly yet. The old code was working correcly, except that in this case of the below 'issue0' it would mark the 'overdue' one instead of the 'ineffective'. The rest worked perfectly. Any idea how I can get to that?

[ryan_mayu · Accepted solution]

@Roym 

pls try this

Column = 
VAR _id=MAXX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&('Table'[Status]="Effective"||'Table'[Status]="Ineffective")),'Table'[ID])
VAR _status=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[ID]=_id+1),'Table'[Status])
VAR _status2=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[ID]=_id),'Table'[Status])
return if(ISBLANK(_id),if('Table'[ID]=CALCULATE(max('Table'[ID]),ALLEXCEPT('Table','Table'[Name])),"Highest ID"),if(_status=""&&_id='Table'[ID],"Highest ID",if((_status="Overdue"||_status="Open")&&_id='Table'[ID]&&'Table'[Status]="Ineffective","Highest ID",if(_status2<>"Ineffective"&&'Table'[ID]=_id+1,"Highest ID"))))

pls see the attachment below

[Roym]

Awesome! This works perfectly! Thanks again for all the help!!!!

[ryan_mayu]

you are welcome

[Roym]

Awesome! you are the man. Thanks for the help!!!

[ryan_mayu · Accepted solution]

@Roym 

one thing want to be mentioned. if you have other issues, you need to modify the DAX

Column = 
VAR _id=maxx(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&('Table'[Status]="Effective"||'Table'[Status]="Ineffective")&&'Table'[ID]<>CALCULATE(max('Table'[ID]),ALLEXCEPT('Table','Table'[Name]))),'Table'[ID])
return if('Table'[ID]=_id+1,"highest value")

[Roym]

I was just trying to that 🙂 Again, really appreciate the help!!

[ryan_mayu]

you are welcome