cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srdjanmish
Helper I
Helper I

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:   

  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?

1 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.

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.

View solution in original post

4 REPLIES 4
v-lid-msft
Community Support
Community Support

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] )
    )

 

 

7.PNG

 

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] )
    )

 

8.PNG

 


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.

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.

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!

 

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.

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.

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors