Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during AI Skills Fest. This week only. Secure your voucher now.

Reply
Tinus1905
Resolver I
Resolver I

First value to find after column value

‎02-22-2024 03:52 AM

Hi,

 

In PowerBi query editor I want to find the first value based on date and name. 

This is what I have:

 

DateEmployeeHoursSick
1-1-2024Dave81
2-1-2024Dave61
3-1-2024Dave81
4-1-2024Dave41
5-1-2024Dave30
6-1-2024Dave80
7-1-2024Dave80
8-1-2024Dave80
9-1-2024Dave80
10-1-2024Dave80
11-1-2024Dave80
12-1-2024Caroline80
13-1-2024Caroline80
14-1-2024Caroline80
15-1-2024Caroline80
16-1-2024Caroline80
17-1-2024Caroline80
18-1-2024Caroline80
19-1-2024Caroline80
20-1-2024Caroline80
21-1-2024Caroline81
22-1-2024Caroline81
23-1-2024Caroline81
24-1-2024Caroline81
25-1-2024Caroline80
26-1-2024Dave80
27-1-2024Dave81
28-1-2024Dave81
29-1-2024Dave81
30-1-2024Dave80
31-1-2024Dave80
1-2-2024Dave80
2-2-2024Dave80
3-2-2024Dave80
4-2-2024Dave80
5-2-2024Dave80
6-2-2024Dave80
7-2-2024Dave80
8-2-2024Dave80
9-2-2024Dave80
10-2-2024Dave81
11-2-2024Dave81
12-2-2024Dave81
13-2-2024Dave80

 

And this is what I want:

 

DateEmployeeHoursSickFirst_sickSick_hoursWorking_hours
1-1-2024Dave818  
2-1-2024Dave61 6 
3-1-2024Dave81 8 
4-1-2024Dave41 4 
5-1-2024Dave30  3
6-1-2024Dave80  8
7-1-2024Dave80  8
8-1-2024Dave80  8
9-1-2024Dave80  8
10-1-2024Dave80  8
11-1-2024Dave80  8
12-1-2024Caroline80  8
13-1-2024Caroline80  8
14-1-2024Caroline80  8
15-1-2024Caroline80  8
16-1-2024Caroline80  8
17-1-2024Caroline80  8
18-1-2024Caroline80  8
19-1-2024Caroline80  8
20-1-2024Caroline80  8
21-1-2024Caroline818  
22-1-2024Caroline81 8 
23-1-2024Caroline81 8 
24-1-2024Caroline81 8 
25-1-2024Caroline80  8
26-1-2024Dave80  8
27-1-2024Dave818  
28-1-2024Dave81 8 
29-1-2024Dave81 8 
30-1-2024Dave80  8
31-1-2024Dave80  8
1-2-2024Dave80  8
2-2-2024Dave80  8
3-2-2024Dave80  8
4-2-2024Dave80  8
5-2-2024Dave80  8
6-2-2024Dave80  8
7-2-2024Dave80  8
8-2-2024Dave80  8
9-2-2024Dave80  8
10-2-2024Dave818  
11-2-2024Dave81 8 
12-2-2024Dave81 8 
13-2-2024Dave80  8

 

So the first time an employee is sick after a working day, then a column with 'First_sick'. 

Labels:
Message 1 of 4
548 Views
0
3 REPLIES 3
Tinus1905
Resolver I
Resolver I

‎02-22-2024 10:29 PM

Someone else has a solution?

Message 4 of 4
497 Views
0
grazitti_sapna
Super User
Super User

‎02-22-2024 04:36 AM

Hi @Tinus1905 , 
You can try using:- =
if [Sick] = 1 and ([Employee] <> #"Shifted Row"[Employee] or #"Shifted Row"[Sick] = 0) then [Hours] else null

and for Sick hours:-

= if [First_sick] <> null then [First_sick] else null
and for working hours.
= if [First_sick] = null then [Hours] else null
This should give you the appropriate columns.
Thank you

Message 2 of 4
522 Views
0
Tinus1905
Resolver I
Resolver I
In response to grazitti_sapna

‎02-22-2024 05:09 AM

@grazitti_sapna when I use if [Sick] = 1 and ([Employee] <> #"Shifted Row"[Employee] or #"Shifted Row"[Sick] = 0) then [Hours] else null in a custom column, I get the same results as column "Hours". 

The result must be:

 

Tinus1905_0-1708607373047.png

 

Message 3 of 4
516 Views
0

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All