Measure filter ignoring established relationships?

[Anonymous]

I'm experiencing different results between a measure and a visual with what seems to be identical filters, but are yielding different results. I've narrowed it down to what seems to be a difference in how the relationship between tables are being handled, but I could use an explanation.

I've created below example which reproduces the problem.

Relationships between tables:

Content of "Items" table showing the items without related items.

Here you can see the result. In the picture both the measure is visible in the top and also the applied filter for the matrix. As can be seen the result (SUM) differs dramatically. Also in the matrix you can see the number of related items, and it's clear to see that the matrix is including items without relations. I also believe this is the correct result (maybe?) as the relationship from the table "ID" doesn't go both ways. 

Please do note that this question only concerns the different result of what I thought was an identical approach, and not the fact that I could just change the relationship.

Hope someone is able to explain the difference 

[BJL]

One workaround that I have found is to deactivate the relationships in the model affecting your measure. No relationship is then the default and USERELATIONSHIP can be used in the measures that need to be connected. This obvisouly removes some functionality and isn't as effective if you have a complex report. 

[Anonymous]

To further complicate things I tried adding a slicer on 'ID'[ID] and limiting it to 6. Apparently the measure was affected but not the matrix. I checked that they are linked (has interactions enabled).

[Anonymous]

I've come to the temporary conclusion that using FILTER inside CALCULATION, such as the above measure, forces a filtering relationship with direction of "Both" - even though no such relationship has been established between the tables. Please do correct me if I'm wrong! 

Wrapping the measure in a CALCULATE function and CROSSFILTER with direction "none" set as the expression (or just adding it to the original CALCULATION expression in this case also works) yields the same result as the matrix:

Total sales items with relations = CALCULATE(CALCULATE(sum(Sales[Value]);filter(Items;Items[NumberOfRelated]>0));
CROSSFILTER(Sales[ID];'ID'[ID];None)
)

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

For the sceanario, I think you need to understand about context of DAX. Regarding measures, it might be affected by the row context. So the result may not be the same when you put it into the charts that contains different columns. And if you use Calculate and Filter, the filter context will be changed to the content that you writed in filter().

Perhaps you might consider taking some time learning about context. There is a blog  that you can reference firstly.

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.