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.
I am trying to find differences between two tables carrying the same data but from different sources. I need to find differences. Can anyone please support!
Table 1:
Employee_Code
Salary_Month
Net_Salary
Table 2:
Employee_Code
Salary_Month
Net_Salary
Custom Calendar:
Date {from 1st Jan 2018 till 31st Jan 2018}
Month
Direct Relationship:
Table 1[Employee_code], Table 2[Employee_code]
Table 1[Salary_month], Calendar[Month]
Indirect Relationship:
Table 2[Salary_month], Calendar[Month]
I am trying to find the difference was in which month, unfortunately, both the below DAX does not calculate.
DAX Formula used:
Difference1 = CALCULATE(SUM('Table 1'[Net_Salary]))- CALCULATE(SUM('Table 2'[Net_Salary]))
Difference2 = CALCULATE(SUM('Table 1'[Net_Salary]))- CALCULATE(SUM('Table 2'[Net_Salary]), USERELATIONSHIP('Table2'[Salary_Month], 'Calendar'[Month])
Required output:
Employee_Code | Month | Table1[Net_salary] | Table2[Net_salary] | Difference |
1001 | 1 | 5000 | 5000 | 0 |
1002 | 2 | 9000 | 8900 | 100 |
1003 | 2 | 8000 | 9500 | -1500 |
Solved! Go to Solution.
While this can be solved in DAX, the cleaner way to do it is to transform your data so that you have a snowflake schema with two fact tables. What you want to do is create a tables with distinct customer code then link this to both tables, as you have done with the calendar tables. You will also need to delete the relationship between the two fact tables.
This is by far the best way to solve this, it is possible with measures or with a different model.
You may change the relationships and then use the measure Difference1. Attached the sample file for your reference.
Regards,
Cherie
You may change the relationships and then use the measure Difference1. Attached the sample file for your reference.
Regards,
Cherie
Hi Team,
Thank you for the post. This works when we have distinct employee codes in both tables.
In my case, I have payroll comparison of the same data coming from two different sources, for some reason.
To be more precise, I have 1000 plus employees each month. All employees are repeated every month except for a few termination cases and new hires.
When I apply your solution, the table 2 salary is getting aggregated and if I select do not summarize, POWER BI sends out a relationship error.
Please see the screenshots.
You may try to change the relationship to 'Many to Many'.
Regards,
Cherie
I tried but no change in result.
I guess this would work.. Thank you so much.
While this can be solved in DAX, the cleaner way to do it is to transform your data so that you have a snowflake schema with two fact tables. What you want to do is create a tables with distinct customer code then link this to both tables, as you have done with the calendar tables. You will also need to delete the relationship between the two fact tables.
This is by far the best way to solve this, it is possible with measures or with a different model.
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |