Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
Hi Cherry,
Sorry for typo. Yes, you are right, that should be open only.
Please find corrected Table below
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.
For more details, you also could refer to my attachement.
Best Regards,
Cherry
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |