Table Calculation (IF THEN ELSE)

[adarshalok]

Hi Everyone, 

I have 2 tables joined together using Left Out Join.

Master Table
App ID	App Name	Department
1	App1	Department1
1	App1	Department2
2	App2	Department2
3	App3	Department3

Table 2
App ID	App Name	Department
1	App1	Department1
1	App1	Department3
2	App2	Department2
3	App3	Department3

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

[v-cgao-msft · Accepted solution]

Hi @adarshalok ,

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

[HoangHugo]

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")