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

Roym's avatar
Roym
Icon for Helper IV rankHelper IV
5 years ago

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's avatar
ryan_mayu
Icon for Super User rankSuper User
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")

  • Roym's avatar
    Roym
    Icon for Helper IV rankHelper IV
    5 years ago

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

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

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

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

      you are welcome

  • Roym's avatar
    Roym
    Icon for Helper IV rankHelper IV
    5 years ago

    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?

     

    Issue16OverdueHighest value
    Issue15Ineffective 
    Issue14Effective 
    Issue23IneffectiveHighest value
    Issue22Ineffective 
    Issue21Ineffective 

     

    • 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?