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
AiolosZhao
Memorable Member
Memorable Member

With using Many-to-Many relationship, when I use measure or sum the amount, some value don't show

Hi all, 

I have two tables:

T1:

TYPEKEYNAMEAMOUNT
TYPE1AAAA12
TYPE2AAAA12
TYPE1BBBB13
TYPE2CCCC14
TYPE1DDDD11

 

T2:

KEYCUSTOMER
AAAA2
AAAA3
BBBB2
CCCC2

 

And the column KEY is the relationship key between these two tables; it's a many-to-many relationship.

 

And when I create a table in my power bi:

With using Many-to-Many relationship, when I use measure or sum the amount, some value don't show1.PNG

 

Question 1: You can see the AMOUNT of D1 is missing. Why?

Question 2: After I change the column sort like the below image, the AMOUNT is also missing, but after I change the format of AMOUNT to "Don't Summarize", then the AMOUNT show up. Why?

 

With using Many-to-Many relationship, when I use measure or sum the amount, some value don't show2.PNG

 

Thanks in advance.

Aiolos Zhao

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION

I have given you some advice about data modeling - that advice is correct.  I have taken a closer look at your sample data.  What you need to understand about Power BI is that the rows in the tables will filter the other tables in the model (in the direction of the filter arrows only).

 

Back to your questions.

1. The reason the value D1 is missing is due to filtering.  When you place Name and Customer onto the rows in the table, you are putting filters on both of those columns.  In otherwords, you are saying "Show all the data where Name = D1 and Customer = Blank.  There is no such data in your model, and that is why it is not showing.  If you remove the Customer from the visual, then you will see the value.

 

2. When you say "don't summarise", you are changing the behaviour of the model.  You are now filtering on all 3 columns.  Power BI shows the list of values for all 3 columns.

 

The test data you have shared is not very descriptive, so it is not clear what it is or how it should be used.  Power BI is a semantic data modelling tool.  It is designed to accept entitiy based dimension tables and also fact tables (dimensional modelling).    I can't advise you on how to set up your data because there is no context in the table names.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

11 REPLIES 11

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.