why relationship didn't propagate with allexcept()

[Jeanxyz]

According to the defintion of allexcept(), this formula removes all the filters except the column specified in the formula. 

My questions is if the filter is retained in a specific column, shouldn't relationship propagation continue to work for that column?

In my data model, I have a product category table which is linked to sales table. I then write a measure which sums up sales amount ignoring any  filter context unless there is a filter on product category. When I call this measure in a pivot table which uses Product Category[product category] in the row, it looks relationship didn't get propagated. It gives the same results despite the product category context.  

sales of all except category2 = calculate(sum(sales[sales amount]),allexcept('sales','sales'[product category]))

Why allexcept() doesn't take external filter context (i.e., pivot table)?  Don't calculate() always copy external filter context to the new filter context? 

Here is the sample file. https://www.dropbox.com/s/8nzzp6mxlt4871x/filter%20test.pbix?dl=0

[bcdobbs · Accepted solution]

Hi,
In your model the table Product Category is on the one side of the one to many relationship with Sales.

All of the columns of Product Category therefore exist in the expanded table version of Sales (Expanded tables in DAX - SQLBI). 

ALLEXCEPT when used as a calculate modifer removes all filters from the entire expanded table of sales apart from the specified column. ALLEXCEPT – DAX Guide

In the measure that doesn't work the matrix is adding 'Product Category'[product category] into the filter context but ALLEXCEPT is then removing it because it's in the expanded table. 

I generally try to avoid ALLEXCEPT as a calculate modifer as it's very easy to write code that breaks easily. You're better in general using a REMOVEFILTERS, VALUES pattern explained in Using ALLEXCEPT versus ALL and VALUES - SQLBI

[chahineatallah]

Hi Ben

But why if i put product category from sales table , then this measure will work 

as i know filters propagate from 1 to many , am not getting the concept that if i take the product category from product table, the filter should also propagate to the many section even if i have allexcept , its a bit confusing

[bcdobbs]

Have a read of this article:

https://www.sqlbi.com/articles/expanded-tables-in-dax/

The whole thing, but real focus on the section on filter propegation. Let me know if that helps or not.

[parry2k]

@Jeanxyz you will do this 

sales of all except category2 = calculate(sum(sales[sales amount]),allexcept('sales','Product Category'[product category]))

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡