Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Table Calculation (IF THEN ELSE)

Hi Everyone, 

 

I have 2 tables joined together using Left Out Join.

Master Table
App IDApp NameDepartment
1App1Department1
1App1Department2
2App2Department2
3App3Department3

 

Table 2
App IDApp NameDepartment
1App1Department1
1App1Department3
2App2Department2
3App3Department3

 

I would like to check which App ID & App Name has a mismatch in terms of Department in form of a table. I used a custom formula to check that (IF MasterTable.Department = Table2Department, "MATCH",, "MISMATCH") and use Mismatch to filter out the results. But the results also contains the scenario where MasterTable.App1 has been flagged as mismatch because MAsterTable.App1.Department2 <> Table2.App1.Department1 (But since MasterTable.App1.Department1 is already matched with Table2.App1.Department1), how do I incorporate that exclusion in my formula? 

 

Thank you in advance

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please new two calculated column:

    KEY = 'Table2'[App ID]&'Table2'[App Name]&'Table2'[Department]
    Check = 
VAR _key = 'Master Table'[App ID] & 'Master Table'[App Name] & 'Master Table'[Department]
VAR _count = CALCULATE(COUNTROWS('Table2'),FILTER(ALL('Table2'),'Table2'[KEY]=_key))
VAR _result = IF(_count<>BLANK(),"MATCH","MISMATCH")
RETURN
_result

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • HoangHugo's avatar
    HoangHugo
    Solution Specialist
    3 years ago

    Hi,

    in table 2,

     

    Match/Mistmach column = IF( COUNTROWS(CALCULATETABLE(Master table,'Master table[NameDepartment] = EARLIER ( 'Table 2[NameDepartment]),'Master table[App] = EARLIER ( 'Table 2[App])))>0,"Match","Mismatch")

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please new two calculated column:

    KEY = 'Table2'[App ID]&'Table2'[App Name]&'Table2'[Department]
    Check = 
VAR _key = 'Master Table'[App ID] & 'Master Table'[App Name] & 'Master Table'[Department]
VAR _count = CALCULATE(COUNTROWS('Table2'),FILTER(ALL('Table2'),'Table2'[KEY]=_key))
VAR _result = IF(_count<>BLANK(),"MATCH","MISMATCH")
RETURN
_result

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data