Solved: Re: No blank row in matrix when using many to many...

Jasper_NL · ‎10-30-2019

Hi everyone,

I have run into this problem and I'm not sure how to get across this behaviour. Here's the deal.

When I have a one to many relationship and ask for a matrix: a blank row shows up. As expected.

When there's a many to many relationship, with a filter in the same direction, I would expect to have the same result. But there isn't:

I understand that every line is doubled. I would expect that. But where's my blank? The turnover of employee 5 and without employee is missing?

How can I fix this?

dax · ‎10-30-2019

Hi Jasper_NL,

As I know, the major differences between relationships with a many-many cardinality and the more typical Many-1 relationships are as follows:

The values shown don't include a blank row that accounts for mismatched rows in the other table. Nor do the values account for rows where the column used in the relationship in the other table is null.
It isn't possible to use the RELATED() function, because more than one row could be related.
Using the ALL() function on a table doesn't remove filters that are applied to other, related tables by a many-to-many relationship.

You could refer to Relationships with a many-many cardinality in Power BI Desktop and Analysis Services Tabular: Many-To-Many Relationships, Bridge Tables, and Blank Members for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_Mathur · ‎10-30-2019

Hi,

Why are you creating a many to many relationship? Why not just stay with a many to 1 relationship? What is the problem with the first 2 visuals that you have shared?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Jasper_NL · ‎10-31-2019

I made a dummy file because I can't share our customer data. There was a valid reason to have a many-to-many relationship in the case I can't share, but I need the blank values to be presented. I will work out a way to develop a one-to-many relationship. Thanks for your help.

dax · ‎10-30-2019

Hi Jasper_NL,

As I know, the major differences between relationships with a many-many cardinality and the more typical Many-1 relationships are as follows:

The values shown don't include a blank row that accounts for mismatched rows in the other table. Nor do the values account for rows where the column used in the relationship in the other table is null.
It isn't possible to use the RELATED() function, because more than one row could be related.
Using the ALL() function on a table doesn't remove filters that are applied to other, related tables by a many-to-many relationship.

You could refer to Relationships with a many-many cardinality in Power BI Desktop and Analysis Services Tabular: Many-To-Many Relationships, Bridge Tables, and Blank Members for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.