cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Measure filter ignoring established relationships?

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:

Relationships.PNG

 

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

Ites.PNG

 

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. 

Measure filter vs visual filter.PNG

 

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 

1 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.

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.

View solution in original post

4 REPLIES 4
BJL
New Member

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
Not applicable

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).

Slicer added.PNG

Anonymous
Not applicable

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)
)

 

 

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.

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors