Calculate Table

[Abhilekh]

Hi, 

I am new to Power BI and I am trying to create a dashboard to check for data gaps across different IT systems. 

I have 2 tables with around 40 columns each. I need to find out if the value in Table1 is exactly the same as value in Table 2, If not then the rows with unmatching values should be stored in another table. 

For Eg: Table 1 ( Employee ID , Firstname, Lastname, country) 

            Table 2 ( Employee ID, Firstname, Lastname, country )

Table to Calculate: ( Only Rows that do not match)

CountryMismatch( Employee ID, Firstname, Lastname, Table1.Country, Table2.Country) 

Table 1 and Table 2 relationship exists on EmployeeID which is a unique value. 

The idea is to build the dashboard capturing the count of mismatching values but also to have data of mismatching values for Audit purposes. I was able  to implement above on SQL and Mysql but would like to learn doing it in PowerBI. 

[v-jiascu-msft]

Hi @Abhilekh,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @Abhilekh,

Please check out the demo in the attachment. The formula could be as follows.

Table =
SELECTCOLUMNS (
    FILTER (
        NATURALINNERJOIN ( 'Table1', 'Table2' ),
        'Table1'[Country] <> 'Table2'[Country]
    ),
    "EmployeeID", 'Table1'[Employee ID],
    "Firstname", 'Table1'[Firstname],
    "Lastname", 'Table1'[Lastname],
    "Table1 Country", 'Table1'[Country],
    "Table2 Country", 'Table2'[Country]
)

Best Regards,

Dale