Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
peterhui50
Helper III
Helper III

Relationship issue -- displaying a simple table

I have a data model here,

 

peterhui50_0-1620064916191.png

 

[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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Picture4.png

 

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

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


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-05-03 232646.png

 

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

Screenshot 2021-05-03 232925.pngScreenshot 2021-05-03 233000.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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!

Jihwan_Kim
Super User
Super User

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.

 

Picture4.png

 

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

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


Go to My LinkedIn Page


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?

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.

 

Picture5.png

 

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

 

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


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.