Forum Discussion
Sum together values from multiple tables based on unique ID
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.
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.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- Fowmy
Super User
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 🙂
V-lianl-msftCommunity Support
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.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
