Merge Two Tables of Different Measurements by TIME and ID into single table?

[PowerOper]

I have two tables, each are similar except that they contain different types of measured values, for example one contains pressure data, and the other has temperature data.

I would like create a new table that merges the two tables so that I have the two measurements, pressure and temperature, in the same table for each id and time.

As an example, here are the two source tables I would like to merge. My apologies for the formatting. The forum site isn't loading properly for me.

PRESSURE_TABLE                                                           TEMPERATURE_TABLE

id      time                        pressure                                       id      time                        temperature
A      2019-12-13 1:00     5000                                             A      2019-12-13 1:00     12

B      2019-12-13 1:00     5200                                             B      2019-12-13 1:00     15

The resulting table after the merge would look like this:

id      time                        pressure         temperature
A      2019-12-13 1:00     5000               12
B      2019-12-13 1:00     5200               15

[az38]

Hi @PowerOper 

if i understand correct it shoud be enough to add calculated column to table pressure or temperature like this:

Temperature = lookupvalue('TEMPERATURE_TABLE'[temperature];
'TEMPERATURE_TABLE'[id];[id]
'TEMPERATURE_TABLE'[time];[time]
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[PowerOper]

Thanks @az38. I've tried that but it returns #ERROR with the error message "A table of multiple values was supplied where a single value was expected."

[az38]

@PowerOper 

it means that you have more then one row that satisfied both of your conditions.

you need to check data or define another rule

do not hesitate to give a kudo to useful posts and mark solutions as solution

[PowerOper]

@az38 When I perform the lookup manually, I don't see duplicates or multiple rows. I do have the 2 tables joined to another dates table on Timestamp, maybe that is causing the issue. I generated this third table as a distinct union of the timestamp fields from the 2 tables. Must be something to do with my relationships. Thanks

[PowerOper]

Yeah I'm probably going about this the wrong way. The two tables are actually derived from another table, so perhaps I should be trying to manipulate that one instead. The source table has columns [Timestamp], [Value], [Measured Name], [ID] and in the Measured Name field it has "Temperature" or "Pressure" to describe what value it is. I had created two separate tables to derive Temperature and Pressure by Timestamp and ID. What I really want to do probably is just pivot the original table so that the values are grouped for [Measured Name] columns. Is there a way to PIVOT with DAX? I'm going to google that @az38

[PowerOper]

OK well this was easy. I just had to pivot the [Measured Name] column in the original M Query. I'm not sure what I didnt' think of that before. My apologies Cheers

[az38]

@PowerOper 

thats great! good luck!

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

so, @PowerOper we need to look at your data model to help you

do not hesitate to give a kudo to useful posts and mark solutions as solution