REMOVEFILTERS and filter propagation Puzzle

[juan_pablo]

Hi, I have the following model:

With the following data in each table:

Products:

Colors:

Sales

I created the following "useless" measure which calculates the sales of the selected product removing the filters on the Colors table

Sales Amount = 
CALCULATE(
    SUM(Sales[Value]),
    REMOVEFILTERS(Colors)
)

In principle, removing the filters on the Colors table which is NOT needed to calculate the sales of a product and it doesn't even has filters applied, should not affect the result of the SUM(Sales[Value]), nevertheless, by some weird reason REMOVEFILTERS(Colors) ALSO removes the filter on the Product table! and I get the sum of all product sales:

(the slicer contains the Product column from the Prodcuts table)

This doesn't make any sense. Not only the Colors table is not needed to calculate sales, but also the direction of the relationship between Colors and Products can't allow filter propagation from Colors to Products. Why is REMOVEFILTERS(Colors) removing the filter on Product from the Products table?

What is happening?

(I dont' need a workaround, I just want to understand this behavior)

Thank you

[v-ssriganesh]

Hi @juan_pablo,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @cengizhanarslan, @Gabry, @ArwaAldoud & @luisoliveira89 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[cengizhanarslan]

Please try the measure below:

Sales Amount =
CALCULATE(
    SUM( Sales[Value] ),
    REMOVEFILTERS( Colors[YourColorColumn] )
)

[Gabry]

Hey,

I think it's because of the expanded table; you can read the article by my fellow Italians:

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

[juan_pablo]

How would the expanded table of Colors affect the expanded table of Sales? Can you elavorate on this? (I think I understand expanded tables)

[Gabry · Accepted solution]

I think the expanded table of colors contains also the products because it's a many to One relationship, filter direction in this case doesn't matter, so removing all the filter onto the colors (expanded) table removes also the filter on products. This Is my under standing but It could be wrong

[juan_pablo]

Hi @Gabry , after some research, you where right. When using REMOVEFILTERS(Colors) I'm removing the filters on the complete expanded table, so it's equivalent to REMOVEFILTERS(Color) & REMOVEFILTERS(Products).

Thank you. 

[ArwaAldoud]

Hi @juan_pablo 

try to use this

Sales Amount =
CALCULATE(
SUM(Sales[Value]),
REMOVEFILTERS(Colors),
KEEPFILTERS(VALUES(Products[Product]))
)

If this response was helpful, please accept it as a solution and give kudos to support other community members!

[luisoliveira89]

Could you try to have the field explicit rather than the whole table?

REMOVEFILTERS(Colors[Color])