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 B in one cohesive table, however Power BI gives me an error and says there no relationships. I am thinking though there is a join table connecting them, but at the end of the day, this is a many to many relationship which is why Power BI cannot display the table.

 

I wanted a table like this.

 

 From Table AFrom Table B
IDNameName_2
1ApplePie
1OrangePie
2BananaSplit
3StrawberryDackery
4TomateSauce

 

Is the error due to the many to many relationship? why can't PBI display this?

 

thanks!

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

5 Replies

  • 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

         

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    A most straightforward way is to merge the tables in PQ

    let
    Source = Table.NestedJoin(#"Table A", "ID", #"Table B", "ID", "TB"),
    #"Expanded TB" = Table.ExpandTableColumn(Source, "TB", {"Name_2"}, {"Name_2"})
in
    #"Expanded TB"

     

    DAX formulae for calculated column and measure by leveraging relationships in the data model,

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

      Thank you! this is very helpful too. I just had though PBI would just let me drag in the column. I think I can use this as well. Thank you once again!