Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all!
I need your help to solve this case :).
I have two different tables. The first one (EV_Tickets) contains the ticket number (RFC_NUMBER) and a Request ID. The second table (EV_Actions) contains multiple actions for each ticket and is connected with the first table using ACTION_REQUEST_ID column with REQUEST_ID, which may contain multiple values of REQUEST_ID.
The idea is to create a column in EV_Tickets that can extract the following information: extract the GROUP in EV_Actions table but only the one that has ACTION_END_DATE_UT column empty.
Here an example:
EV_tickets table
RFC_NUMBER | REQUEST_ID |
P180801_000033 | 837296 |
EV_Actions
ACTION_REQUEST_ID | ACTION_CREATION_DATE | ACTION_END_DATE_UT | GROUP |
837296 | 01/08/2018 8:04 | 01/08/2018 8:04 | Service Desk N1 |
837296 | 01/08/2018 8:06 | 01/08/2018 8:06 | |
837296 | 01/08/2018 8:06 | 01/08/2018 8:06 | |
837296 | 01/08/2018 8:06 | 01/08/2018 8:06 | Service Desk N1 |
837296 | 01/08/2018 8:06 | 01/08/2018 8:06 | Service Desk N1 |
837296 | 01/08/2018 8:06 | 01/08/2018 8:06 | |
837296 | 01/08/2018 8:07 | Desarrollo | |
837296 | 01/08/2018 8:07 | 01/08/2018 8:07 | Service Desk N1 |
837296 | 01/08/2018 8:08 | 01/08/2018 8:08 | Service Desk N1 |
837296 | 01/08/2018 8:08 | 01/08/2018 8:08 | Service Desk N1 |
837296 | 02/08/2018 5:40 | 02/08/2018 5:40 | Desarrollo |
837296 | 02/08/2018 5:41 | 02/08/2018 5:41 | Desarrollo |
837296 | 02/08/2018 5:42 | 02/08/2018 5:42 | Desarrollo |
In this example, I need to extract "Desarrollo" GROUP as it's the only one that has the end date empty.
I tried with Lookup function and filters but I didn't managed to extract it correctly.
Here you have the link to the file if you need it to test.
Thanks a lot 🙂
Solved! Go to Solution.
@Anonymous
As a calculated column, try this
Action Group = CONCATENATEX ( FILTER ( RELATEDTABLE ( EV_Actions ), ISBLANK ( EV_Actions[ACTION_END_DATE_UT] ) ), [GROUP], "," )
@Anonymous
As a calculated column, try this
Action Group = CONCATENATEX ( FILTER ( RELATEDTABLE ( EV_Actions ), ISBLANK ( EV_Actions[ACTION_END_DATE_UT] ) ), [GROUP], "," )
It worked perfectly!!! Thanks a lot for your help and fast reply 🙂
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |