ISINSCOPE messes up matrix

[thescrimpton]

I have 3 tables. These are the typical Customer, Product, Sales tables. Sales is just simply the mapping of Customer and what Product they bought. Below are the contents of the tables and how they relate.

Then I created the measure :

IsInScopeFlag =

ISINSCOPE('Product'[ProductName])

For some reason, this measure messes up the row count of my matrix.

Matrix before ISINSCOPE.

Matrix after adding IsInScopeFlag

However I found a workaround to make this work. Instead of referring to the columns 'CustomerName' and 'ProductName' in their respective dimension tables, what I did was to create calculated columns of 'CustomerName' and 'ProductName' in Sales (making it redundant) and usee them in the matrix. For some reason this did the trick. I also created a new measure IsInScope2 to refer to the newly created column.

RedundantCustomerName =

RELATED(Customer[CustomerName])

RedundantProductName =

RELATED('Product'[ProductName])

IsInScopeFlag2 =

ISINSCOPE(Sales[RedundantProductName])

 
My questions are:
1. Why does the first IsInScope measure messes up the matrix? (I am thinking this is about the relationship.)
2. Is my workaround the only way to do this? Or this there a way to make it work using DAX/Power BI trick without creating redundant columns and maintaining the relationship of the tables.


Thanks Power BI Community!

[Greg_Deckler · Accepted solution]

@thescrimpton The answer to your first question is that ISINSCOPE just checks if the column is or is not in scope and returns true or false. Since you aren't aggregating any value, basically the filtering going on with the table relationships doesn't come into play when calculating this measure so you will always get back True or False and since you get a value back for each Product, then that row is displayed in your matrix. It would be the same thing as if you created a measure that returned a constant value.

Would need you to post sample data to mock this up. Good chance there is a different trick for this. One thing you might try is making your Product to Sales relationship bi-directional.

[thescrimpton]

I didn't realize this is happening on the background until you add a measure. Thanks for the reply @Greg_Deckler.

Since you aren't aggregating any value, basically the filtering going on with the table relationships doesn't come into play when calculating this measure so you will always get back True or False and since you get a value back for each Product, then that row is displayed in your matrix.