Forum Discussion
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'[...
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.
Hi,
Without introducing the date, "Employee rows" counts all occurences of each employee. By including the date, the count is all occurrence of each employee for each date.
Employee rows without Date = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Employee]=EARLIER(Data[Employee])))Employee rows = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Employee]=EARLIER(Data[Employee])&&Data[Date]=EARLIER(Data[Date])))
The result I am looking for is to show the "Code" for each Employee on each date. Concatenated on the last occurence if there are multiple occurrences on any given date.
Below, the "Status" of Employee 1 and 2 should be "Working+Holiday" and not "Working+Holiday+Other"
| Employee | Date | Start Time | End Time | Code | Rank | Employee rows | Employee rows without Date | Status |
| 1 | 11-Oct-22 | 07:00:00 | 15:00:00 | Working | 1 | 2 | 71 | |
| 1 | 11-Oct-22 | 15:00:00 | 19:00:00 | Holiday | 2 | 2 | 71 | Working+Holiday+Other |
| 2 | 11-Oct-22 | 07:00:00 | 11:00:00 | Holiday | 1 | 2 | 73 | |
| 2 | 11-Oct-22 | 11:00:00 | 19:00:00 | Working | 2 | 2 | 73 | Working+Holiday+Other |
| 3 | 11-Oct-22 | 19:00:00 | 03:00:00 | Working | 1 | 1 | 83 | Working |
Hope this helps.
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.
I did try this before and wasn't get the same result but this works now.
Thank you for all your help Ashish.
You are welcome.