Forum Discussion
Constant Measure breaking my One-to-Many Relationship - Why?
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.
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.
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.