Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

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

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

Users
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

Need Help

Watsky
4 years ago
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!

6 Replies

Watsky
Solution Sage
4 years ago
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
  Not applicable
  4 years ago
  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
    Solution Sage
    4 years ago
    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!
Anonymous
Not applicable
4 years ago
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