cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srdjanmish Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Highlighted
v-lid-msft Super Contributor
Super Contributor

Re: Joining one field from table to another table with through table

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.

View solution in original post

4 REPLIES 4
v-lid-msft Super Contributor
Super Contributor

Re: Joining one field from table to another table with through table

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.

srdjanmish Frequent Visitor
Frequent Visitor

Re: Joining one field from table to another table with through table

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!

 

Highlighted
v-lid-msft Super Contributor
Super Contributor

Re: Joining one field from table to another table with through table

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.

View solution in original post

srdjanmish Frequent Visitor
Frequent Visitor

Re: Joining one field from table to another table with through table

@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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 183 members 2,156 guests
Please welcome our newest community members: