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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Write these calculated column formulas

Employee rows = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Employee]=EARLIER(Data[Employee])))
Status = if(Data[Employee rows]=2,if(Data[Rank]=1,BLANK(),CONCATENATEX(filter(VALUES(Data[Code]),Data[Employee]=EARLIER(Data[Employee])),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.