Forum Discussion

M0n5ta09's avatar
M0n5ta09
Helper I
3 years ago
Solved

Column Values based on Rank and Date

Hi,   I have the table below which uses RANKX to show the occurrence of each employee for each day.   RANKX ( FILTER ( 'TABLE', 'TABLE'[Employee] = EARLIER ( 'TABLE'[Employee] )     && 'TABLE'[...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Try this calculated column formula

    Status = if(Data[Employee rows]=2,if(Data[Rank]=1,BLANK(),CONCATENATEX(filter(Data,Data[Employee]=EARLIER(Data[Employee])&&Data[Date]=EARLIER(Data[Date])),Data[Code],"+")),Data[Code])

    Hope this helps.

M0n5ta09's avatar
M0n5ta09
Helper I
3 years ago

Thank you Ashish,

I amended the "Employee rows" so it counts the rows for each Employee for each date.

Employee rows = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Employee]=EARLIER(Data[Employee])&&'Data'[Date]=EARLIER(Data[Date])))

Expanding my data (1/9/22-31/12/22), "Status" is now including the value of "Code" from other dates.

I've tried amending "Status" to

Status = if(Data[Employee rows]=2,if(Data[Rank]=1,BLANK(),CONCATENATEX(filter(VALUES(Data[Code]),Data[Employee]=EARLIER(Data[Employee])&&Data[Date]=EARLIER(Data[Date])),Data[Code],"+")),Data[Code])

but the result is the same.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Now that you have added date in the Employee rows calculated column formula, your requirement is different from the one you had earlier posted.  Please explain the revised question in simple Engligh and show the expected result.  Also, share some data to work with.