Forum Discussion
add column from another table by using filter
Hello all,
I have two related tables(lets say table1 and table2) as below:
I want to move the column 'Duration_min' in table2 to table1 but it has to be filter by bay_id and group by description_id. the final goal is to calculate the total usage of water like 'average_flow'*'duration_min'.
I would be so approciated if anyone could help with this problem.
Thanks in advance.
Hi , Anonymous
It is suggest to move the column 'AverageFlow' in table1 to table2 rather than move the column 'Duration_min' in table2 to table1 due to the relationship between table1 and table2(one-to-many rather than many-to_one ).
You can create caluculated column as below :
Average Flow = LOOKUPVALUE('Table 1'[AverageFlow],'Table 1'[bay_id],'Table 2'[bay_id])total usage of water = 'Table 2'[Duration_Min]*'Table 2'[Average Flow]The result will show as below:
You also can create measure as below:
total usage of water = SELECTEDVALUE('Table 1'[AverageFlow])*SELECTEDVALUE('Table 2'[Duration_Min])You can also hide the columns you don’t need by reducing the row width manually in table visual.
Best Regards,
Community Support Team _ Eason
2 Replies
Anonymous , This how you can mode data from one table to another first one will only work in case 1-M join to copy in the table on M side
Item Name = RELATED('item'[Brand]) // Item to sales
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City]) // City to salesMonth Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date]) // From Date to sales
Hi , Anonymous
It is suggest to move the column 'AverageFlow' in table1 to table2 rather than move the column 'Duration_min' in table2 to table1 due to the relationship between table1 and table2(one-to-many rather than many-to_one ).
You can create caluculated column as below :
Average Flow = LOOKUPVALUE('Table 1'[AverageFlow],'Table 1'[bay_id],'Table 2'[bay_id])total usage of water = 'Table 2'[Duration_Min]*'Table 2'[Average Flow]The result will show as below:
You also can create measure as below:
total usage of water = SELECTEDVALUE('Table 1'[AverageFlow])*SELECTEDVALUE('Table 2'[Duration_Min])You can also hide the columns you don’t need by reducing the row width manually in table visual.
Best Regards,
Community Support Team _ Eason
