Search value from one table to another with a condition

[Anonymous]

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.

Link to report

Thanks a lot 🙂

[Zubair_Muhammad · Accepted solution]

@Anonymous 

As a calculated column, try this

Action Group =
CONCATENATEX (
    FILTER (
        RELATEDTABLE ( EV_Actions ),
        ISBLANK ( EV_Actions[ACTION_END_DATE_UT] )
    ),
    [GROUP],
    ","
)

[Anonymous]

It worked perfectly!!! Thanks a lot for your help and fast reply 🙂