Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community,
I need some help with building a calculated column.
Here's the scenario:
if 4 records/rows (events) happened to the same entity within 4 hours I need to flag those events.
example data:
Entity | Event | Desired Flag
123 | 3/17/2020 2:04:00 AM | No
123 | 3/17/2020 3:20:00 AM | No
456 | 3/17/2020 9:00:00 AM | Yes
456 | 3/17/2020 9:40:00 AM | Yes
456 | 3/17/2020 10:30:00 AM | Yes
456 | 3/17/2020 11:10:00 AM | Yes
456 | 3/20/2020 12:00:00 PM | No
Thanks.
Try this
Flag V2 =
VAR eventsIn4hWindow_ =
CALCULATETABLE (
DISTINCT ( Table1[Event] ),
ALLEXCEPT ( Table1, Table1[Entity] ),
Table1[Event] >= (EARLIER ( Table1[Event] ) - ( 4 / 24 )),
Table1[Event]<= (EARLIER ( Table1[Event] ) + ( 4 / 24 ))
)
VAR checkT_ =
FILTER (
eventsIn4hWindow_,
COUNTROWS (
FILTER (
eventsIn4hWindow_,
Table1[Event] >= EARLIER ( Table1[Event] ) && Table1[Event]<= (EARLIER ( Table1[Event] ) + ( 4 / 24 ))
)
) >=4
)
RETURN
IF ( ISEMPTY ( checkT_ ), "No", "Yes" )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @805
You need to specify bit more.
Are we looking at four events within the same day? CAn the number of events in one day be > 4?
What happens if there are more than 4 events than fall in the same four hour period?
What in a case like this? What would be the right flags?
456 | 3/17/2020 9:00:00 AM | Yes
456 | 3/17/2020 9:40:00 AM | Yes
456 | 3/17/2020 10:30:00 AM | Yes
456 | 3/17/2020 11:10:00 AM | Yes
456 | 3/17/2020 12:30:00 AM | Yes
456 | 3/17/2020 13:10:00 AM | Yes
456 | 3/17/2020 15:10:00 AM | Yes
456 | 3/17/2020 16:10:00 AM | Yes
456 | 3/17/2020 17:05:00 AM | Yes
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hey @AIB Looking to flag events that occur in any 4 hour period so it's possible they could cross into the following day (11PM-3 AM.) The number of events can be greater than 4 in a 4 hour period.
- What happens if there are more than 4 events than fall in the same four hour period? - All events for that entity need to be flagged. For context this is related to MTP (massive transfusion protocol) related to blood transfusions.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |