Skip to main content
cancel
Showing results for 
Search instead 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

Reply
M0n5ta09
Helper I
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)
 
EmployeeDateStart TimeEnd TimeCodeRANKX (Occurrence)
11/10/2207:0019:00Working1
21/10/2207:0013:00Working1
21/10/2213:0019:00Holiday2
32/10/2207:0019:00Working1
42/10/2207:0013:00Working1
42/10/2213:0019:00Holiday2
52/10/2207:0019:00Working1

 

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

EmployeeDateStart TimeEnd TimeCodeRANKX (Occurrence)Status
11/10/2207:0019:00Working1Working
21/10/2207:0013:00Working1 
21/10/2213:0019:00Holiday2Working + Holiday
32/10/2207:0019:00Working1Working
42/10/2207:0013:00Working1 
42/10/2213:0019:00Holiday2Working + Holiday
52/10/2207:0019:00Working1Working

 

Any pointers would be appreciated.

1 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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
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.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

M0n5ta09_1-1670843381666.png

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.

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
https://www.linkedin.com/in/excelenthusiasts/

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])))

 

M0n5ta09_0-1670849660224.png

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"

EmployeeDateStart TimeEnd TimeCodeRankEmployee rowsEmployee rows without DateStatus
111-Oct-2207:00:0015:00:00Working1271 
111-Oct-2215:00:0019:00:00Holiday2271Working+Holiday+Other
211-Oct-2207:00:0011:00:00Holiday1273 
211-Oct-2211:00:0019:00:00Working2273Working+Holiday+Other
311-Oct-2219:00:0003:00:00Working1183Working

 

Link to Sample.pbix 

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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

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

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.