To Bring information of two related tables

[renatofrancavr]

Hi,

I have two tables. ("Base N2" and "Base N3", 1-to-many relationship). I need to create a chart to show:

1) The "ticket numbers" that are in the two tables.

2) The "ticket numbers" that are in table "Base N2" but are not in table "Base N3".

In the table "Base N3 ", I can use the "related" in the formula and I can show the data that are in the two tables (picture below), however, I do not I can use "reletad" in the formula in "Base N2" to resolved the item 2, because of the relationship of the tables.

Can you help me?

Thanks in advance.

Best Regards,

Renato

[renatofrancavr · Accepted solution]

Hi @v-huizhn-msft,

Reinforcing my question...

With the formula below, I can get the "tickets number" from the BaseN2 table and that are also in the BaseN3 table.

Now, I need to show all tikets that are in BaseN2 and are not in Base N3. (Follows below an example in excel )

Follows the relationship between the two tables.

Best Regards,

Renato França

[v-huizhn-msft]

Hi @renatofrancavr,

Could you please post the formula including related function for further analysis?

Best Regards,
Angelia

[renatofrancavr · Accepted solution]

Hi @v-huizhn-msft,

Reinforcing my question...

With the formula below, I can get the "tickets number" from the BaseN2 table and that are also in the BaseN3 table.

Now, I need to show all tikets that are in BaseN2 and are not in Base N3. (Follows below an example in excel )

Follows the relationship between the two tables.

Best Regards,

Renato França

[v-huizhn-msft · Accepted solution]

Hi @renatofrancavr,

You'd better add middle table including the distinct value in column ticket, then create a 1 to 1 relationship between base2 and mid-table. I try to reproduce your scenario and get desired result as follows.

I have table2 and table3 like the screenshot, and there are one(Table2) to many(Table3) relationship between the two tables.

 

First, create a middle table using the following formula and get a new table.

Mid-Table = DISTINCT(Table3[Ticket])

Then create a relationship between table2 and middle table.

In table 2, you can create a calculated column using the formula below. Create a table, select the Employee and column as values, you will get the expected result.

Result = IF(ISBLANK(RELATED('Mid-Table'[Ticket])),"Not found",RELATED('Mid-Table'[Ticket]))

If you have any other question, please let me know.

Best Regards,
Angelia