add column from another table by using filter

[Anonymous]

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.

[v-easonf-msft · Accepted solution]

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.

pbix attached

Best Regards,
Community Support Team _ Eason

[amitchandak]

@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 sales

Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date]) // From Date to sales