topic Re: Merge Two Tables of Different Measurements by TIME and ID into single table? in DAX Commands and Tips

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

PowerOper — Fri, 13 Dec 2019 19:58:54 GMT

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

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

az38 — Sat, 14 Dec 2019 08:28:27 GMT

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

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

PowerOper — Mon, 16 Dec 2019 16:40:15 GMT

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."

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

az38 — Mon, 16 Dec 2019 16:59:54 GMT

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

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

PowerOper — Mon, 16 Dec 2019 17:13:35 GMT

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

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

az38 — Mon, 16 Dec 2019 17:23:09 GMT

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

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

PowerOper — Mon, 16 Dec 2019 17:29:45 GMT

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

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

PowerOper — Mon, 16 Dec 2019 17:40:28 GMT

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

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

az38 — Mon, 16 Dec 2019 18:20:17 GMT

PowerOper

thats great! good luck!

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