Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Regular Visitor

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.