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

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

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

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