Sum together values from multiple tables based on unique ID

[Anonymous]

Hello,

I have been generating a monthly report that has two columns "AssetID" and "DaysOnline". The AssetID is a unique identifier used across several tables to denote a device, while days online is how many days an asset communicated for a specific month. At the moment I have 10 reports, each in a different table. Example format below.

Asset ID	DaysOnline
ABC	10
XYZ	15

I have a master table for all assets that are registered and I would like to create a new column in this table that adds together the days online for each asset from the monthly reports. For example, if asset ABC was online for 10 days in January, 15 in February, and 10 in March, the new column would list ABC as having 35 days online and do the same for each asset in the list. Example format for master file below.

Asset ID	Asset Type	Registration Date	DaysOnline
ABC	A	1/1/2020	35
XYZ	B	2/1/2020	27

I know I could do something similar in excel with index and match functions, but I am new to DAX and need help figuring out how to do this.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you have ten different tables to store monthly data, and a master table contains all the asset IDs.

Try using append queries in the query editor.

Create a one to many relationship between the master table and the append table.

Sample .pbix 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

If your Master Asset Table has a relationship with other tables, you can add a column like

Day on Line = 
sumx( RELATEDTABLE(TABLE1), TABLE1[DAYS] ) +  sumx( RELATEDTABLE(TABLE2), TABLE2[DAYS] ) + so on til 10.

I suggest you should append (combine all those tables to one for better modeling)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn