Time stamp to event UUID

ChipTz — Tue, 26 Nov 2019 12:02:49 GMT

Hi, I have the following two example tables in my data:

EVENTS

TimeStamp	Node
01/10/2019 11:23	A
01/10/2019 12:43	A
01/10/2019 12:55	B
01/10/2019 16:23	A
02/10/2019 09:27	B
02/10/2019 11:23	A
02/10/2019 16:22	C
02/10/2019 19:45	C
03/10/2019 07:03	A
03/10/2019 09:11	A
03/10/2019 11:55	B
04/10/2019 11:14	C

CHECKS

Node	UUID	ENDDATE
A	FADFSA	01/10/2019 20:30
B	TREVFD	02/10/2019 10:00
A	TFDASN	03/10/2019 16:15
C	GRVWSI	03/10/2019 19:30
B	TRHYZH	04/10/2019 00:30
C	JIUOEW	04/10/2019 11:15

I need to add a calculated column to the events table with the following conditions

EVENTS[Node] = CHECKS[Node]
EVENTS[TimeStamp] <= CHECKS[ENDDATE]
EVENTS[TimeStamp] > PREVIOUS CHECKS[ENDDATE]

The end result would look like this:

EVENTS

TimeStamp	Node	Calculated_Matching_UUID
01/10/2019 11:23	A	FADFSA
01/10/2019 12:43	A	FADFSA
01/10/2019 12:55	B	TREVFD
01/10/2019 16:23	A	FADFSA
02/10/2019 09:27	B	TREVFD
02/10/2019 11:23	A	TFDASN
02/10/2019 16:22	C	GRVWSI
02/10/2019 19:45	C	GRVWSI
03/10/2019 07:03	A	TFDASN
03/10/2019 09:11	A	TFDASN
03/10/2019 11:55	B	TRHYZH
04/10/2019 11:14	C	JIUOEW

I tried to look for a solution but couldn't find one. How can I use DAX to build this calculated column?
Thanks!

Re: Time stamp to event UUID

Anonymous — Wed, 27 Nov 2019 05:54:00 GMT

ChipTz try this and check if it works for you

Column = CALCULATE(MIN(Check[UUID]),FILTER(Check,Check[ENDDATE]>=Event[TimeStamp] && Check[Node]=Event[Node]))

Re: Time stamp to event UUID

ChipTz — Thu, 28 Nov 2019 18:30:33 GMT

Thanks for the fast feedback. It did work on the example data I posted but it is not working on my real data. I need to check what is wrong wiht my data 😐

I don't get why MIN is the function that solves this, tough. I was expecting something as SELECTEDVALUE.

topic Re: Time stamp to event UUID in DAX Commands and Tips

Time stamp to event UUID

Re: Time stamp to event UUID

Re: Time stamp to event UUID