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 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.

 

IDNameStatusMonth
4Issue1OpenJuly
3Issue1OpenMarch
2Issue1OverdueFebruary
1Issue1EffectiveJanuary
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

13 Replies

  • 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")