topic Re: IF function based on date in DAX Commands and Tips

IF function based on date

Anonymous — Mon, 27 Mar 2023 07:44:57 GMT

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!

Re: IF function based on date

wdx223_Daniel — Mon, 27 Mar 2023 08:03:07 GMT

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

Re: IF function based on date

Anonymous — Mon, 27 Mar 2023 08:21:29 GMT

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!

Re: IF function based on date

wdx223_Daniel — Wed, 29 Mar 2023 02:06:12 GMT

please show your real code