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

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:

Tabel1.PNG
Table2:

Tabel2.PNG
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,

Table3.PNG
Thanks,
Ganesh

1 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.

Capture.PNG

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

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Table3.PNG

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

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Cherry,

 

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

 

Please find corrected Table below

Table3.PNG

 

Thanks & Regards,

Ganesh

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.

Capture.PNG

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

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.