Forum Discussion
Adding a simple constant valued measure stops breaks relationships in drill down table visual - why?
Hey Anonymous ,
Thanks for showing and clarifying. Try this.
Modify your measure so that it is looking at the userid before returning 123.
MyConstantValue = VAR userid = SELECTEDVALUE(users[userid]) RETURN IF(userid<>"",123,0)Then filter MyConstantValue to greater than 0.
With John selected:
With Jane selected:
The issue is that there isn't a relationship between your measure and the tables so Power BI isn't sure how to handle it. Because of the lack of a relationship that is also why you were seeing blank values. When there is ambiguity Power BI will give all dimensions all possible values.
So with the measure and filter change you remove all the ambiguous values leaving only the values you are needing.
Hope this helps!
I'm struggling to understand "there isn't a relationship between your measure and the tables", because I have a measure that does reference some tables, and as far as I can tell there are unabiguous relationships between the tables of all columns referenced in my measure and table visual, yet I'm seeing this phenomenon where a relationships is ignored and Power BI gives all possible values to some fields. I'm not asking for a solution but perhaps you could refer me to documentation that touches on this. Could it be that table visuals including multiple facts with multiple common dimensions do this when a measure is added that manipulates one of the conformed dimensions' filters?
Edit:
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.
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 relationships with CROSSFILTER(..., ..., None) and substituting it with a filter on the relationship key on the many side of that relationship.