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, 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!
Solved! Go to Solution.
@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]))
@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]))
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.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |