Forum Discussion

srdjanmish's avatar
srdjanmish
Icon for Helper I rankHelper I
7 years ago
Solved

Joining one field from table to another table with through table

Hello everybody.   I am new, so, please, be gentle if I make mistakes, or sound as a beginner :)   My problem is next:   I have 3 tables:   TABLE 1 columns:    acID acRelT3ID .... other c...
Need Help
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    Hi srdjanmish ,

     

    How about add one column contain table name in each t3 table, then union them as one table in the power query editor? Then we can just use the measure as following formula.

     

    DisplayValue =
VAR tableName =
    LOOKUPVALUE ( T2[relationTableName], T2[acID], MAX ( 'T1'[acID] ) )
RETURN
    LOOKUPVALUE (
        T3Union[DispalyValue],
        T3Union[TableName], tableName,
        T3[acRelT3ID], MAX ( 'T1'[acRelT3ID] )
    )

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi srdjanmish ,

 

We can create a measure using following formula and put it into a table visual to meet your requirement, but we need to put all the table in T2 into the SWITCH function.

 

DisplayValue = 
VAR tableName =
    LOOKUPVALUE ( T2[relationTableName], T2[acID], MAX ( 'T1'[acID] ) )
VAR T3ID =
    MAX ( 'T1'[acRelT3ID] )
RETURN
    SWITCH (
        tableName,
        "TableX", MAXX ( FILTER ( 'TableX', [acRelT3ID] = T3ID ), [DisplayValue] ),
        "tblAbc", MAXX ( FILTER ( 'tblAbc', [acRelT3ID] = T3ID ), [DisplayValue] )
    )

 

 

 

Or we can create a calculated column in T1.

 

DisplayValueColumn = 
VAR tableName =
    LOOKUPVALUE ( T2[relationTableName], T2[acID], [acID] )
VAR T3ID = [acRelT3ID]
RETURN
    SWITCH (
        tableName,
        "TableX", MAXX ( FILTER ( 'TableX', [acRelT3ID] = T3ID ), [DisplayValue] ),
        "tblAbc", MAXX ( FILTER ( 'tblAbc', [acRelT3ID] = T3ID ), [DisplayValue] )
    )

 

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • srdjanmish's avatar
    srdjanmish
    Icon for Helper I rankHelper I
    7 years ago

    Hi, and thank you for quick answer v-lid-msft ...

     

    This idea is good, but problem is that SWITCH in formula. Here, in this example, it is easy, 'cause there are 2 tables, but in real database, list of databases is over 15-20 tables, and it is dynamic (more tables can be added). If there is solution to that, I would be very gratefull!

     

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi srdjanmish ,

       

      How about add one column contain table name in each t3 table, then union them as one table in the power query editor? Then we can just use the measure as following formula.

       

      DisplayValue =
VAR tableName =
    LOOKUPVALUE ( T2[relationTableName], T2[acID], MAX ( 'T1'[acID] ) )
RETURN
    LOOKUPVALUE (
        T3Union[DispalyValue],
        T3Union[TableName], tableName,
        T3[acRelT3ID], MAX ( 'T1'[acRelT3ID] )
    )

      Best regards,

      Community Support Team _ Dong Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • srdjanmish's avatar
        srdjanmish
        Icon for Helper I rankHelper I
        7 years ago

        v-lid-msft, you are right, maybe that can be a solution... Good idea... I will try as soon as I get time.