Adding a simple constant valued measure stops breaks relationships in drill down table visual - why?

[Anonymous]

Hi All,

I have a simple data model and an accompanying Power BI driven by XLSX file.   

Model

Every User in the users table has 1 or more sales records in the Sales table

List of tables

1. Users
2. Sales

Sample data

Sales

User

Power BI Visual

Overview table

visual showing all Sales persons from Users table

Details table

This shows all the sales records for that sales person

Sample measure

I have a dummy table called AllMeasures just as a place holder for all measures.  I have created the following measure, which as you can see returns a constant value.

MyConstantValue = 123

Problem

When I add the measure MyConstantValue to the Sales table visual on the drill through report Tab, the table visual starts displaying records for all users.  In the example below, the table visual is now showing jane under userid.

Why? If I remove the measure MyContantValue, all works fine.

Accompany XLSX and PBIX

PBIX

Excel data file

[Anonymous]

Hi @Watsky,

I can demonstate this inexplicable behaviour even withou having any drill through reports.

Step 1 - A table which shows userid and total sales per userid

All the fields in the table visual are from Sales table

Step 2 - Add First Name and Last Name to the table

We want to enhanced the visualization by bringing in more user details

Step 3 - Now add the constant Measure

I am unable to explain how the 2 extra rows got added when I added the measure to the table visual.  

Thank you,

Sau

[Watsky]

Hey @Anonymous ,

Looks like you're pulling the userid field from the Sales table. Instead, pull it from the users table and change your filter to the userid from the users table. 

[Anonymous]

Hello @Watsky ,

Thanks for replying. Your approach looks promising. However, if you notice closely, you will see that Sales records for Jan Doe have also been pulled in your Visualization. (red rectangle)

If I were to remove the constant valued measure, then there is no problem at all.

Without the measure

Looks good. The drill down table shows records for John Doe only

With the measure

Putting the measure back into the visual - we see all sales records !!! We only wanted John's records.

Any thoughts?

Sau

[Watsky · Accepted solution]

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!

[nixonba2]

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.

[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 relationships with CROSSFILTER(..., ..., None) and substituting it with a filter on the relationship key on the many side of that relationship.