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

     

     

     

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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