Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

IF function based on date

Hi all,

Have a DAX calculation I need to create, was hoping I could get some help with it. I have the below table for a recruiter. It looks at employees, the date they're leaving the company, and the fee associated with their leaving.

 

I need to create a DAX calculation that takes the first 8 employees with values for Y_Fee_Calc (ie > 0) based on leavedate and gives the result "CAP", otherwise "NOCAP".

 

So in this dummy data below, it would take the first 8 based on their leave date (down as far as Michelle Harris) and give them "CAP", the last two would have "NOCAP". Is this possible?

 

 

 

 

 

Thanks!

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    NewColumn=VAR _n=Table[FullName] RETURN IF(Table[Y_Fee_Calc]=0||COUNTROWS(FILTER(TOPN(8,FILTER(Table,Table[Y_Fee_Calc]>0),Table[Leavedate],ASC,Table[Y_Fee_Calc],DESC),Table[FullName]=_n)=0,"NOCAP","CAP")

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you so much for the quick reply! The calculation is definitely on the right track, but I seem to be getting an error when I enter it - COUNTROWS has too many filters, the maximum is 1. Is there any  way around this?

       

      Thanks again!