FILTER function query

Question

Hi,&nbsp;I'm trying to find the value of SalesAmount for all products with "SQLBI" brand or color "Red" while not altering filters in other columns (It's a question on the sqlbi website).&nbsp;I know this statment is correct:&nbsp;Calculate (
    [SalesAmount],
    FILTER (
        ALL (
            Product[Color],
            Product[Brand]
        ),
        Product[Color] = "Red" || Product[Brand] = "SQLBI"
    )
)But please can you tell me why the code below is wrong? I thought that the line Product[Color] = "Red" || Product[Brand] = "SQLBI" would automatically override any filters for Product[Color] and Product[Brand]? So the two snippets of code would be equivalent?&nbsp;CALCULATE (
    [SalesAmount],
    FILTER (
        Product,
        Product[Color] = "Red" || Product[Brand] = "SQLBI"
        )
    )&nbsp;Thanks for any help,&nbsp;CM

stachu · Answer

I think it's best to show by example, take table like this:

Color
Brand
Amount

Red
SQLBI
1

Red
A
1

Red
B
1

Blue
SQLBI
1

Blue
A
1

Green
B
1

the measures return following results:

let's take the first row in incorrect - unmodified filter context is Color = Blue &amp;&amp; Brand = AWhich is effectively just 1 row table that looks like this:
table like this:

Color
Brand
Amount

Blue
A
1

the numbers of rows that are Red or SQLBI in this table = 0, hence blank
So ALL allows to get the value that's visible in the Total row, rather than one that is specific to partivular filter context
&nbsp;
hope that helps

anonymous · Answer

Thank you&nbsp;Stachu , is it correct to say that an OR statement doesn't override the existing filter context, but normal statments like the two below (without the "OR" condition) do override the existing filter context?&nbsp;Product[Color] = "Red",Product[Brand] = "SQLBI"&nbsp;Many Thanks,CM

stachu · Answer

actually my explanation is not accurate, I got very confused because of the multiple columns
this article does a good job explaining what's happening in the back
https://www.sqlbi.com/articles/filter-arguments-in-calculate/

anonymous · Answer

Thanks&nbsp;Stachu ,&nbsp;Looking at the link I see the section "Table Filter" which says:&nbsp;"Using a table filter, you inherit the filter argument existing for the Product table, so you will not include a product Red or of the Contoso brand if it was not present in the existing filter. What is more important, you will not override the existing filter on such a column. Thus, if you have a slicer filtering the brand Proseware, you will see the sales amount of only the products Red belonging to Proseware brand, ignoring any product of the Contoso brand."&nbsp;What I don't understand is the sentance "What is more important, you will not override the existing filter on such a column" - I thought the FILTER function adopts all existing filters and overrides them where a filter is defined in the FILTER function? I don't understand why an OR filter within a FILTER function would not override the existing filter?&nbsp;CM

stachu · Answer

FILTER does override for a single column, with multiple columns that seems to not be the case.
&nbsp;
You could try doing an analysis with ISFILTERED and ISCROSSFILTERED, it could be related to that, but I am not really sure why exactly it acts this way.
&nbsp;
In the conclusions of the article they mention a book that's supposed to have more detailed analysis, maybe it's worth looking into

Forum Discussion

FILTER function query

5 Replies

Related content

Caclulate function over riding filter?

filter function

Regular Expression Functions (REGEX) in Power Query

Query contains unsupported function. Function name: Resource.Access

Question about Power Query functions

Color	Brand	Amount
Red	SQLBI	1
Red	SQLBI	1
Red	A	1
Red	B	1
Blue	SQLBI	1
Blue	A	1
Green	B	1