Why is adding a measure breaking my slicer filters

[craigchamberlai]

I have a table with users, each user occurs multiple times as I project values into the future and each row represents a user/year.

UserID	Year	Other Values
1	2020	Complex projected values.
1	2021	Complex projected values.

I have a dropdown slicer so one year is always selected and I can treat my model as distinct in all of the generated reports.

I have another table that describes User Siblings, of which each user may have several.

UserID	SiblingID	Other Values
1	2	Basic values.
1	3	Basic values.

They have a many:many relationship with filtering flowing from the first.

If I try to represent these linked values in a table report, then I get x by x rows for each row, as the filter on year is ignored. 

UserID	Year	SiblingID
1	2020	2
1	2021	2
1	2020	3
1	2021	3

I would have expected simply

UserID	Year	SiblingID
1	2020	2
1	2020	3

If I choose to sumerise the data, such as count value, then the rows are consolidated.  The problem is I wanted to us a measure that combines various elements from the linked table and I have no option to sumerise the measure.  Oddly I get a number of blank values and one acurate measure.

Is this in anyway expected behavoir of a many to many relationship?  I know the model would ideally not have any many to many relationships but I moved to projecting values into a common table as the complexity of trying to achieve the same with measures was horendous I was constantly fighting the lack of row context.

Some posts suggest having an intermediary table with say unique user ids in and having two many to one relationships focusing on this.  But would I again have a problem focusing the filter context down to rows of the linked table? 

Here is a link to a very simple example, it actually works fine so I know that this is not expected behaviour.  If I find a solution I will post it.

https://drive.google.com/file/d/1-5AHnXQB8qGaSYz87vrU4nMdOTzzM8Bv/view?usp=sharing

[craigchamberlai · Accepted solution]

So it was to do with the measure I had in my real report.  I wanted to display some defualt values in the case there

was a blank so had a measure like this. 

Count = COALESCE( COUNT('Siblings'[Data]) ,0)

This creating values for rows that where linked but blank.  I'm still suprised by this behavior but least I know where it is coming from.  It could be a bit to do with the visual which perhaps doesn't have a root table with bits added on and treats all tables with columns featured as equal.

Count = COALESCE( COUNT('Siblings'[Data]) ,0)

The following is a more realistic example. Say one or more Data may be blank or you are combining information from various nullable columns.  First check if a column that should always have a value is valid and return a blank if not.

DataConcat = IF(COUNT('Siblings'[Data]) > 0 , CONCATENATEX('Siblings', [Data], ","), BLANK())