Joining one field from table to another table with through table

[srdjanmish]

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:   

1. acID
2. acRelT3ID
3. .... other columns doesn't matter

Table 2 columns:

1. acID
2. relationTableName (it can be some ID, unique, that points to table with values

Table 3 (this i table with name from table 2, column 2, it is not one table, it is several tables) columns:

1. acRelT3ID (key column, it is value used in table 1)
2. DisplayValue

My question:

    I need to join table 1 and table 3 through table 2. Case is, in table 1, I have multiple acID's, and acRelT3ID is from one of tables that relates through table 2... Let me give you hint that can, maybe, help you:

Example values

T1:

110011    32

120010    16

T2:

110011    tableX

120010    tblAbc

T3a (name of table is tableX):

32    Toronto

33    Alaska

T3b (name of table is tblAbc):

16    White

17    Black

18    Yellow

I need to get data in shape:

110011    33    tableX     Toronto

120010    16    tblAbc     White

Is this possible?

[v-lid-msft]

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]

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 · Accepted solution]

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]

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