Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Search value from one table to another with a condition

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_NUMBERREQUEST_ID
P180801_000033837296

 

EV_Actions

ACTION_REQUEST_IDACTION_CREATION_DATEACTION_END_DATE_UTGROUP
83729601/08/2018 8:0401/08/2018 8:04Service Desk N1
83729601/08/2018 8:0601/08/2018 8:06 
83729601/08/2018 8:0601/08/2018 8:06 
83729601/08/2018 8:0601/08/2018 8:06Service Desk N1
83729601/08/2018 8:0601/08/2018 8:06Service Desk N1
83729601/08/2018 8:0601/08/2018 8:06 
83729601/08/2018 8:07 Desarrollo
83729601/08/2018 8:0701/08/2018 8:07Service Desk N1
83729601/08/2018 8:0801/08/2018 8:08Service Desk N1
83729601/08/2018 8:0801/08/2018 8:08Service Desk N1
83729602/08/2018 5:4002/08/2018 5:40Desarrollo
83729602/08/2018 5:4102/08/2018 5:41Desarrollo
83729602/08/2018 5:4202/08/2018 5:42Desarrollo

 

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 🙂

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

As a calculated column, try this

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

As a calculated column, try this

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.