Forum Discussion

peterhui50's avatar
peterhui50
Icon for Helper III rankHelper III
5 years ago
Solved

Relationship issue -- displaying a simple table

I have a data model here,     [Table_A] is joined with [JOIN] table through Many :1  [Table_B] is joined with [JOIN] through Many:1 as well.   I want to display both the Table A and Table...
General Comment
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, peterhui50 

    Please check the below picture and the sample pbix file's link down below.

    I could not know whether you want a calculated column or a measure, so I created both.

     

     

    TableB Name Measure =
    IF (
    ISFILTERED ( 'Join'[ID] ),
    MAXX (
    TableA,
    LOOKUPVALUE ( TableB[Name_2], TableB[ID], RELATED ( 'Join'[ID] ) )
    )
    )
     
    TableB Name CC =
    LOOKUPVALUE ( TableB[Name_2], TableB[ID], RELATED ( 'Join'[ID] ) )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, peterhui50 

Please check the below picture and the sample pbix file's link down below.

I could not know whether you want a calculated column or a measure, so I created both.

 

 

TableB Name Measure =
IF (
ISFILTERED ( 'Join'[ID] ),
MAXX (
TableA,
LOOKUPVALUE ( TableB[Name_2], TableB[ID], RELATED ( 'Join'[ID] ) )
)
)
 
TableB Name CC =
LOOKUPVALUE ( TableB[Name_2], TableB[ID], RELATED ( 'Join'[ID] ) )
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

peterhui50's avatar
peterhui50
Icon for Helper III rankHelper III
5 years ago

Thank you! it works, but why can't Power BI just display the two columns simply by dragging and dropping? Is it because it is a many:many relationship?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, peterhui50 

    Thank you for your feedback.

    I am not sure where I can create many to many relationship.

    However, if you change the relationship like below, no need to create a measure, and you can just drag and drop.

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM