Matrix is displaying too many rows of related tables

[_JoSi_DA]

Hi all,
I stumbled over a strange behaviour, which I didn't expect. Acutally pretty simple config...
I have two tables groups and objects, which have a 1-to-*-relationship. The object table itself is linked to other tables...
Group Table

Id	GroupName
1	Group1
2	Group2
3	Group3

Object Table

Id	ObjectName	GroupId
1	A	1
2	B	1
3	C	2
etc.

Now, I have a matrix visual showing the groupnames as rows, which works as it should.

However, as soon as I also insert the object names as second row level in the matrix, it is showing every object underneath each of the groups, although not every object is indeed in that group.

I was expecting Power BI to recognize via the relation between the two tables, which objects are part of the group and display accordingly?!

Thanks for the support 🙂

Greetings,
J.

[Anonymous]

Hi @_JoSi_DA ,

I tried to reproduce the problem, but I created the data and it showed correctly. If you can, provide some screenshots of the error and remember to protect your privacy.

Best Regards,

Stephen Tao

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

[_JoSi_DA]

Hey Stephen,

thanks for your reply.

Indeed it is working like in your example as long as I do not add additional values/measures to the maxtrix.

As soon as I add a meaure, which e.g. calculates the quantity of articles in each category, it shows all articles underneath each category. The data model/realtionships are as follows:

Furthermore I have two dropdowns on the page in order to filter by year and BusinessUnitName (see yellow fields). Quantity Measures is pretty simple:

WW Quantity = COALESCE(SUM(WW_ReportedData[Total]), 0)

Many thanks, J.

[_JoSi_DA]

One more note: I guess the issue is caused by the fact, that since CALCULATE always removes the filter context in general, this does also apply to the filter, which is given by the relationship between Group --> Object. So IMO there must be a way to tell Power BI to remove the filter context, but keep the filter given by the Group --> Object relationship?! I already tried some wiht ALLEXCEPT, but I didn't work so far.

[brokencornets]

Obvious question, but is there a relationship between Object|Group ID and Group|ID fields showing in the data model?