cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
craigchamberlai
Regular Visitor

Why is adding a measure breaking my slicer filters

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.

 

UserIDYearOther Values
12020Complex projected values.
12021Complex 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.

UserIDSiblingIDOther Values
12Basic values.
13Basic 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. 

 

UserIDYearSiblingID
120202
120212
120203
120213

 

I would have expected simply

UserIDYearSiblingID
120202
120203

 

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

  

1 ACCEPTED SOLUTION
craigchamberlai
Regular Visitor

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())

View solution in original post

1 REPLY 1
craigchamberlai
Regular Visitor

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())

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.