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

[AiolosZhao]

Hi all, 

I have two tables:

T1:

TYPE	KEY	NAME	AMOUNT
TYPE1	AAA	A1	2
TYPE2	AAA	A1	2
TYPE1	BBB	B1	3
TYPE2	CCC	C1	4
TYPE1	DDD	D1	1

T2:

KEY	CUSTOMER
AAA	A2
AAA	A3
BBB	B2
CCC	C2

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:

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?

Thanks in advance.

Aiolos Zhao

[AiolosZhao]

Or any documentation about this so I can know more information?

Thanks.

Aiolos Zhao

[MattAllington]

Your new table should also have the Name included.  You should always use columns from this new table in your visual (the text ones).  You can then use the numeric columns from any table.

It is not clear in your image which table the Key column has come from.  My guess is it is not the new table. 

You can read my artile her for more information. https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

[AiolosZhao]

And I have read the documentation you shared with me, thanks.

But I think that's talk about the star schema, and I know the cons and pros for star schema, snow schema, etc.

I didn't find an explanation of my two questions.

Aiolos Zhao

[AiolosZhao]

Hi @MattAllington ,

Thanks for your reply, as you can see, I have two tables like below:

T1:

TYPE	KEY	NAME	AMOUNT
TYPE1	AAA	A1	2
TYPE2	AAA	A1	2
TYPE1	BBB	B1	3
TYPE2	CCC	C1	4
TYPE1	DDD	D1	1

T2:

KEY	CUSTOMER
AAA	A2
AAA	A3
BBB	B2
CCC	C2

For Power BI, what's the best way to make below table:

NAME	AMOUNT	CUSTOMER
A1	2	A2
A1	2	A3
B1	3	B2
C1	4	C2
D1	1

Could you please show and tell me?

Thanks in advance

Aiolos Zhao

[MattAllington]

In the visual you posted above, after you created the third table, which key are you using in the visual?  It should be the key column from the new table.  Is it?

[AiolosZhao]

Do you mean the T3?

The KEY column in T3 is created by manually, just merge all values of KEY from T1 and T2.

You can also forget the T3 I created, just using T1 and T2, I have shown you the table that I want.

What kind of operations should I do now?

Aiolos Zhao

[MattAllington]

I am trying to help you here.  You need the other table (you called it T3).  Create the table as I described and add the other columns that are part of the key too (customer, name etc).

Join the 3 tables as I described

Then use the Key column from T3 and not from the other tables.  

This is how you do it.  If you can't work it out, then post a link to your sample workbook here and I will do it for you.

[AiolosZhao]

Could you please show me what the T3 should look like using below T1/T2?

T1:

TYPE	KEY	NAME	AMOUNT
TYPE1	AAA	A1	2
TYPE2	AAA	A1	2
TYPE1	BBB	B1	3
TYPE2	CCC	C1	4
TYPE1	DDD	D1	1

T2:

KEY	CUSTOMER
AAA	A2
AAA	A3
BBB	B2
CCC	C2

[MattAllington · 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.

[MattAllington]

This is a complex topic. The simple answer is “don’t use many to many unless you know when it works and when it doesn’t”. I suggest you create a new table with a distinct list of all the keys. Join both tables to this new table (1 to many) and use the new table in your visuals.  This is how power BI is designed to work. 

[AiolosZhao]

Hi @MattAllington ,

Thanks for the reply.

Do you mean I need to create a new table like below:

T3:

KEY

AAA

BBB

CCC

DDD

Then I create relationships like below:

But the AMOUNT is also missing in my table:

Or do you mean I need to use "merge query" in edit query?

I think if my data is too large, the merge query would cost too much time.

Aiolos Zhao