cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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'[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.

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Thank you for all your help Ashish.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.