Comparing two table columns which is having Many to many relationship

[Anonymous]

Hi,
Please help me to derive calculated column based on multiple columns, getting it from two tables and those two tables are having Many to Many relationship.

For example, I have
Table1:

Table2:

here, I have to consider Ticket category, if either any of the category ctg-1 or ctg-2 is open, then status should be open at Sub Tikcet level. we don't need to consider ctg-3.

and expected output is like below,

Thanks,
Ganesh

[v-piga-msft]

Hi @Anonymous ,

I have a lilttle confused about your logic.

---

here, I have to consider Ticket category, if either any of the category ctg-1 or ctg-2 is open, then status should be open at Sub Tikcet level. we don't need to consider ctg-3.

---

Based on the logic you described, If I understood correctly that the status should be open the ctg -1 is Closed and the ctg-2 is Open.

However, in your expected output the ctg-1 is closed for ST-203.

If it is convenient, could you describe your logic in more details?

Best Regards,

Cherry

[Anonymous]

Hi Cherry,

Sorry for typo. Yes, you are right, that should be open only. 

Please find corrected Table below

Thanks & Regards,

Ganesh

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

For your logic, please create the calculated column like below in Table2.

Column =
CALCULATE (
    MAX ( 'Table 2'[Status] ),
    FILTER (
        ALLEXCEPT ( 'Table 2', 'Table 2'[Ticket No] ),
        'Table 2'[Ticket Category] = "ctg-1"
            || 'Table 2'[Ticket Category] = "ctg-2"
    )
)

Here is the output.

For more details, you also could refer to my attachement.

Best  Regards,

Cherry