topic Re: FILTER function query in DAX Commands and Tips

FILTER function query

Anonymous — Wed, 19 Jun 2019 11:06:01 GMT

Hi,

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

I know this statment is correct:

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?

CALCULATE (
    [SalesAmount],
    FILTER (
        Product,
        Product[Color] = "Red" || Product[Brand] = "SQLBI"
        )
    )

Thanks for any help,

Re: FILTER function query

Stachu — Wed, 19 Jun 2019 16:13:25 GMT

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

Color	Brand	Amount
Red	SQLBI	1
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 && Brand = A
Which 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

hope that helps

Re: FILTER function query

Anonymous — Thu, 20 Jun 2019 08:13:37 GMT

Thank you 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?

Product[Color] = "Red",

Product[Brand] = "SQLBI"

Many Thanks,

Re: FILTER function query

Stachu — Thu, 20 Jun 2019 09:54:10 GMT

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/

Re: FILTER function query

Anonymous — Thu, 20 Jun 2019 10:20:54 GMT

Thanks Stachu ,

Looking at the link I see the section "Table Filter" which says:

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

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?

Re: FILTER function query

Stachu — Thu, 20 Jun 2019 12:25:15 GMT

FILTER does override for a single column, with multiple columns that seems to not be the case.

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.

In the conclusions of the article they mention a book that's supposed to have more detailed analysis, maybe it's worth looking into