Forum Discussion

Roym's avatar
Roym
Icon for Helper IV rankHelper 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

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
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]<>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's avatar
Roym
Icon for Helper IV rankHelper IV
5 years ago

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's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    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
      Icon for Helper IV rankHelper IV
      5 years ago

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

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

        you are welcome