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.