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: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
anonimox
Frequent Visitor

Calculated Column to Highlight Event that matches Criteria in a Time Interval

‎03-08-2024 10:34 AM

Sample Data:

EmployeeDateStateStart TimeEnd TimeDurationIndicator (The expected calculated column)
Employee 153/8/2024IB12:14:00 PM12:18:00 PM0:00:04:00 
Employee 173/8/2024IB12:14:00 PM12:18:00 PM0:00:04:00 
Employee 203/8/2024Internal12:14:00 PM12:15:00 PM0:00:01:00 
Employee 103/8/2024IB12:14:00 PM12:15:00 PM0:00:01:00 
Employee 123/8/2024NotReady12:15:00 PM12:17:00 PM0:00:02:00 
Employee 23/8/2024Waiting12:15:00 PM12:16:00 PM0:00:01:00 
Employee 103/8/2024ACW12:15:00 PM12:16:00 PM0:00:01:00 
Employee 113/8/2024OB12:15:00 PM12:16:00 PM0:00:01:00 
Employee 193/8/2024ACW12:15:00 PM12:17:00 PM0:00:02:00 
Employee 73/8/2024ACW12:15:00 PM12:18:00 PM0:00:03:00 
Employee 203/8/2024Waiting12:15:00 PM12:30:00 PM0:00:15:00 
Employee 183/8/2024Waiting12:16:00 PM12:41:00 PM0:00:25:00 
Employee 103/8/2024Waiting12:16:00 PM12:17:00 PM0:00:01:00 
Employee 133/8/2024ACW12:16:00 PM12:18:00 PM0:00:02:00 
Employee 63/8/2024Waiting12:16:00 PM12:19:00 PM0:00:03:00 
Employee 103/8/2024ACW12:16:00 PM12:19:00 PM0:00:03:00 
Employee 143/8/2024Waiting12:16:00 PM12:24:00 PM0:00:08:00 
Employee 23/8/2024IB12:16:00 PM12:25:00 PM0:00:09:00 
Employee 113/8/2024Waiting12:16:00 PM12:34:00 PM0:00:18:001
Employee 193/8/2024Waiting12:17:00 PM12:25:00 PM0:00:08:00 
Employee 193/8/2024IB12:25:00 PM12:36:00 PM0:00:11:002
Employee 113/8/2024Internal12:34:00 PM12:35:00 PM0:00:01:00 
Employee 113/8/2024ACW12:35:00 PM12:36:00 PM0:00:01:00 
Employee 73/8/2024Consult12:35:00 PM12:36:00 PM0:00:01:00 
Employee 243/8/2024ACW12:35:00 PM12:36:00 PM0:00:01:00 
Employee 173/8/2024IB12:35:00 PM12:38:00 PM0:00:03:00 
Employee 263/8/2024NotReady12:35:00 PM12:39:00 PM0:00:04:00 

 

Example highlighted in the table above: 
[Emlpoyee 11] was in "Waiting" state between 12:16:00 PM up to 12:34:00 PM
Then  [Employee 19] was in "Waiting" state at 12:17:00 PM.
Then [Employee 19] switched to "IB" state before the [Employee 11] at 12:25 PM. 

Requirement: A calculated column that returns 1 or 2:

Return 1 for the [Employee 11]
Return 2 for the [Employee 19] 

In other words, assign value 1 to the first employee (in our example [Employee 11]) waiting and while that first employee was waiting, there was another employee which switch to a different state before the first employee waiting. 
And assign a value 2 to any employee (in our example [Employee 19] that switched to any of the following states before the first employee waiting: 

[State] = "OB"
or [State] = "IB"
or [State] = "Internal"
or [State] = "Consult"
IMPORTANT: In our example, [Employee 19] was not in "Waiting" status right before [Employee 11] went on "Waiting" status. This needs to be part of the criteria as well. 

NOTE:
The criteria will ignore scenarios where the waiting duration was 1 minute only as there are no seconds provided in the data. 

The main purpose is to isolate this scenario out of the rest of the events. This calculated column will be used later on for a conditional formating in a Matrix. 


Desired result example:

PBI Example.png

 

 Any other ideas are more than welcome. 







Labels:
Message 1 of 6
240 Views
0
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User
In response to anonimox

‎03-19-2024 06:08 PM

see attached

View solution in original post

event criteria.pbix
Message 6 of 6
137 Views
0
5 REPLIES 5
anonimox
Frequent Visitor

‎03-19-2024 07:45 AM

1. @lbendlin What visual is this? 
2. How did you add the Start and End DateTime to the x-axis? 

This visual is good. I only want to be able to highlight the scenario mentioned before. 
What happens is that while one employee was "Waiting" another one switch from state. The logic should be that the "Waiting" employee should have changed from state before anybody else. The employees 11 and 19 were only a spotted example. The main purpose of the visual is to be able to highlight this scenario every time it happens and to avoid to search for the scenario.   

Message 3 of 6
157 Views
0
lbendlin
Super User
Super User
In response to anonimox

‎03-19-2024 08:04 AM

That visual is a simple Deneb based bar chart but with two X axis values (from and to). I can post the Vega Lite code if you like.

 

 

Message 4 of 6
152 Views
0
anonimox
Frequent Visitor
In response to lbendlin

‎03-19-2024 01:02 PM

@lbendlin  Yes please, post the code. I will adapt it to the requirement.  

Message 5 of 6
150 Views
0
lbendlin
Super User
Super User
In response to anonimox

‎03-19-2024 06:08 PM

see attached

event criteria.pbix
Message 6 of 6
138 Views
0
lbendlin
Super User
Super User

‎03-08-2024 06:27 PM

Not following your logic at all.  Meanwhile here is a graphical representation.  What is so special about employees 11 and 19?

 

lbendlin_0-1709951249221.png

 

Message 2 of 6
203 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All