Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |