Column Values based on Rank and Date

[M0n5ta09]

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'[Date] = EARLIER ( 'TABLE'[Date] ) ),

    'TABLE'[Start Time],  , ASC)

Employee	Date	Start Time	End Time	Code	RANKX (Occurrence)
1	1/10/22	07:00	19:00	Working	1
2	1/10/22	07:00	13:00	Working	1
2	1/10/22	13:00	19:00	Holiday	2
3	2/10/22	07:00	19:00	Working	1
4	2/10/22	07:00	13:00	Working	1
4	2/10/22	13:00	19:00	Holiday	2
5	2/10/22	07:00	19:00	Working	1

I am stuck in trying to get a calculated column (Status) to show the 'Code' from both the first and second occurence like below.

Employee	Date	Start Time	End Time	Code	RANKX (Occurrence)	Status
1	1/10/22	07:00	19:00	Working	1	Working
2	1/10/22	07:00	13:00	Working	1
2	1/10/22	13:00	19:00	Holiday	2	Working + Holiday
3	2/10/22	07:00	19:00	Working	1	Working
4	2/10/22	07:00	13:00	Working	1
4	2/10/22	13:00	19:00	Holiday	2	Working + Holiday
5	2/10/22	07:00	19:00	Working	1	Working

Any pointers would be appreciated.

[Ashish_Mathur]

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]

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]

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.

[M0n5ta09]

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

Link to Sample.pbix 

Hope this helps.

[Ashish_Mathur · Accepted solution]

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]

I did try this before and wasn't get the same result but this works now.

Thank you for all your help Ashish.

[Ashish_Mathur]

You are welcome.

[lbendlin]

Why should the status be blank for the first rows of employees 2 and 4 ? Shouldn't that say Working?

[M0n5ta09]

I want to have the "Status" for each Employee for each day.