Forum Discussion

Roym's avatar
Roym
Helper IV
5 years ago
Solved

Show/Count highest ID if

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 Eff...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    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's avatar
    ryan_mayu
    5 years ago

    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's avatar
    ryan_mayu
    5 years ago

    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's avatar
Roym
Helper IV
5 years ago

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

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

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's avatar
    Roym
    Helper IV
    5 years ago

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