Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Hi @Abhilekh,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
User | Count |
---|---|
93 | |
87 | |
77 | |
72 | |
66 |
User | Count |
---|---|
116 | |
107 | |
88 | |
65 | |
63 |