Creating a Data Comparison Table From Two Different Datasets

[Anonymous]

Hi everyone, 

I have been asked to create a data comparison table whereas I ran against a problem I can't manage to solve. The main purpose is to see the difference between the results of the laboratory analysis of a sample. First data is being provided by an on-site facility, and the second one is being provided by an outside laboratory. You can see the data tables below.

 

When I tried to build a comparison table everything is running smooth until I introduce the second dataset. Whenever I add laboratory measured pH values, the table fills itself with values which doesn't exist. For example, in the picture below, for 01.01.2023 DG3 it can be seen that the Laboratory_Measured_pH is equal to 5,36 whereas that is not found in our Laboratory analysis table.

To solve the issue, I tried the combinations between relations, created Digester Reference Table and Date Table. But nothing seems to be solved. I know that I am missing a basic fact but I didn't manage to solve hence this is my first project. 

Thank you for your time.

[johnt75]

I would change the model. Make the relationship from Calendar to Lab Analysis 1-to-many and single direction,  make the relationship from Digester Reference Table to Lab Analysis active and delete the relationship between Lab Analysis and Factory Analysis.

Then create a few measures like

Lab analysis PH = SELECTEDVALUE( 'Lab Analysis'[ph value])

Factory analysis PH = SELECTEDVALUE( 'Factory Analysis'[ph value])

Difference = [Lab analysis PH] - [Factory analysis PH]

Then if you use columns from Calendar and Digester Reference Table on your table visual it should work.