Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Create a Calculated Column/measure from Different Tables

Hi Everyone,   The relationship between 2 tables is 1: many. How can I get the table I would like? Thank you in advance!  
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

hI Anonymous

 

Use this calculated column in Table A

 

G =
DIVIDE ( Table A[E], RELATED ( Table 2[F] ) )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks a lot!

However, after I input "Related", I can't input the column name then. This may be caused by "one to many" relationship.

Could you have any advices to solve this? Thanks!

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    RELATED is used in Many-Side Table to pull information from ONE-SIDE. Its like a VLOOKUP

     

    It works fine when I used your sample data. See the image below

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks a lot!

      I used this as a measure before, so it doesn't work.

       

      Now it works well.

      However, I meet a new problem now. Could you help me to solve this? I would like to get the column/measure filled in grey.

      Thank you in advance!

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi Anonymous

         

        Try this calculated column

         

        =
IF (
    TableA[GROUP] <> "Total",
    RELATED ( Table2[F] ),
    IF (
        TableA[TOTAL GROUP] = "One",
        CALCULATE (
            SUM ( Table2[F] ),
            FILTER ( ALL ( Table2 ), Table2[GROUP] = "A" || Table2[GROUP] = "B" )
        ),
        IF (
            TableA[TOTAL GROUP] = "Two",
            CALCULATE ( SUM ( Table2[F] ), FILTER ( ALL ( Table2 ), Table2[GROUP] = "C" ) )
        )
    )
)