Constant Measure breaking my One-to-Many Relationship - Why?

[bvy]

Very simple data model and report here to demonstrate my issue. I have a Person table and a City table with a one-to-many relationship on CityID as shown below. I entered the data manually. 

I've added MyMeasure to the Person table that just returns a constant value. 

My report has a slicer on Name, and two table visuals to illustrate the issue.
Table1 - Without MyMeasure in the table, it works as expected. It shows the selected user name with their city name. 

Table2 - Whenever I add MyMeasure to the table, it ignores the relationship and returns the selected user associated with ALL cities. 

What is the simplest modification I can make here to correct the issue? Thank you. 

[bvy]

Sharing the file: 
https://www.icloud.com/iclouddrive/0d6d4dwSm8pn6KBjScuq07Y6g#Measure%5FIssue

[Greg_Deckler · Accepted solution]

@bvy Maybe:

MyMeasure = 
    VAR __CityID = MAX('Person'[CityID])
    VAR __CityID2 = MAX('City'[CityID])
RETURN
    IF(__CityID = __CityID2, "Active",BLANK())

As for why, it is because you coded your measure as a constant. So every row will return that constant value for your measure and thus your current results.

[nixonba2]

I get that making a row return a value causes it to appear in a table visual, but I'm struggling to understand how a measure can break relationships and thus cause rows to appear in a table that was already displaying "items with no data". Can you refer me to any documentation regarding these concepts? My measure is not constant but exhibits some of this behaviour, and while I was able to fix it using advice from this thread, I don't like not fully understanding the concepts.

[nixonba2]

I found the answer by turning on Performance Analyzer in PBI Desktop to capture the DAX generated by my table visual, then I read up on the SUMMARIZECOLUMNS() function it uses (on Microsoft website and dax.guide), which explained that it computes the cartesian product of the input columns, ignoring all actual relationships and data rows, then relies on measures returning blank to exclude combinations of the input fields that do not exist in the underlying data/relationships. If you don't give any measures but you reference multiple tables in your table visual, Power BI automatically adds a COUNTROWS() measure, uses it to remove rows, then trims off the column so you never see it.

My measure wasn't working in my table visual because I had fields from multiple tables with a common dimension, and my measure needed to override only one of the relationships between that dimension and the other tables. I fixed it by, rather than overriding the filter on the dimension table, disabling one of its relationships with CROSSFILTER(..., ..., None) and substituting it with a filter on the relationship key on the many side of that relationship.

[bvy]

Hi @Greg_Deckler and thanks for taking time to look at this with me... 

I see where you're heading with this, but your proposed change doesn't work. I've tried some variations also, and they don't work either. See below...

[Greg_Deckler]

@bvy Likely incorrect configuration of the visual or a difference in relationships. City should come from the City table and Name from the Person table. Take a look at the PBIX attached below signature, working great there.

[bvy]

You were right. I had changed the relationsship to two way and forgot to send it back. Appreciate the help.