Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Comparing two table columns which is having Many to many relationship

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

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

     

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        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