Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 d...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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

     

     

     

     

v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

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