Summarize Table within Filter

[dicken]

Hi,

I have a formula


S

ummarize:=CALCULATE (

    [Total sales M],

    FILTER (

        SUMMARIZE ( Sales, Customer[Customer], 'Product'[Product] ),

        Customer[Customer] = "tom"

            && 'Product'[Product] = "a" ) )

My question is why don't I need to use keepfilter to create an 'AND' operation with the external filter context, if I replace summarize with crossjoin, then wrapping in keepfilter would be needed to achieve this, is it to do with what is being sent to CALCULATE?

Richard.

 

[Matt Allington]

You don’t need summarize, or crossjoin. DAX is not SQL. Normally you do not need to recreate these filtered projected tables to do your calculations.

summarize :=
CALCULATE (
[Total sales M],
Customer[Customer] = "tom",
'Product'[Product] = "a"
)

 

[dicken]

You don’t need summarize, or crossjoin. DAX is not SQL. Normally you do not need to recreate these filtered projected tables to do your calculations.

summarize :=
CALCULATE (
[Total sales M],
Customer[Customer] = "tom",
'Product'[Product] = "a"
)

Hello Matt,
Two , well three things, one the above does not see the external filter context, so I get the correct result but for every customer / product
, second it can only be used as an "AND" not "OR" but if I use make a virtual crossjoined table then you can use either,
lastly I was more interested in why the Summarize recognize the external context without the use of keepfilter?
as you can see below the summarize works fine but has no keepfilter.
Richard.

 

[Matt Allington]

1. Yes it does, it is just that it is removed by the hidden ALL. The full syntax is as follows


summarize :=
CALCULATE (
[Total sales M],
FILTER(ALL(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(ALL(Product[Product]),'Product'[Product] = "a")
)

the one I posted is short hand for the one above. The initial filter is removed by the ALL. You can change the behaviour in at least 2 ways


summarize :=
CALCULATE (
[Total sales M],
KEEPFILTERS(Customer[Customer] = "tom"),
KEEPFILTERS('Product'[Product] = "a")
)


summarize :=
CALCULATE (
[Total sales M],
FILTER(VALUES(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(VALUES(Product[Product]),'Product'[Product] = "a")
)

2. It is possible to do OR over 2 columns by using UNION. I’m not saying it’s simple, just that it can be done.

summarize :=
CALCULATE (
[Total sales M],
DISTINCT(
UNION(FILTER(ALL(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(ALL(Product[Product]),'Product'[Product] = "a")))
)

The distinct is needed because Union == Unionall in SQL

3.SUMMARIZE is effectively “expanded tables”. Filters therefore are always embedded implicity in a summarize function. You can read my (long) article here. I cover expanded tables towards the bottom. https://exceleratorbi.com.au/many-many-relationships-dax-explained/

 

[dicken]

1. Yes it does, it is just that it is removed by the hidden ALL. The full syntax is as follows


summarize :=
CALCULATE (
[Total sales M],
FILTER(ALL(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(ALL(Product[Product]),'Product'[Product] = "a")
)

the one I posted is short hand for the one above. The initial filter is removed by the ALL. You can change the behaviour in at least 2 ways


summarize :=
CALCULATE (
[Total sales M],
KEEPFILTERS(Customer[Customer] = "tom"),
KEEPFILTERS('Product'[Product] = "a")
)


summarize :=
CALCULATE (
[Total sales M],
FILTER(VALUES(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(VALUES(Product[Product]),'Product'[Product] = "a")
)

2. It is possible to do OR over 2 columns by using UNION. I’m not saying it’s simple, just that it can be done.

summarize :=
CALCULATE (
[Total sales M],
DISTINCT(
UNION(FILTER(ALL(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(ALL(Product[Product]),'Product'[Product] = "a")))
)

The distinct is needed because Union == Unionall in SQL

3.SUMMARIZE is effectively “expanded tables”. Filters therefore are always embedded implicity in a summarize function. You can read my (long) article here. I cover expanded tables towards the bottom. https://exceleratorbi.com.au/many-many-relationships-dax-explained/

Thank you Matt,
I'll certainly read the article,
I've put

EVALUATE
DISTINCT (
    UNION (
        FILTER ( ALL ( Customer[Customer] ), Customer[Customer] = "tom" ),
        FILTER ( ALL ( Product[Product] ), 'Product'[Product] = "a" )
    )
)

into Studio, and get back a two row table, is that what causes the "OR", as opposed two column equaling 'AND' just guessing
from using 'advanced filter' in excel. As yes i haven't been able to make it work in the actual table, will persevere, I first saw the crossjoin being used in an sqlbi video, can you see any disadvantages to using crossjoin? I did time it against two Keepfilters, in studio over a data set of some 21000 rows and there was no great difference.

Richard.

 

[dicken]

1. Yes it does, it is just that it is removed by the hidden ALL. The full syntax is as follows


summarize :=
CALCULATE (
[Total sales M],
FILTER(ALL(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(ALL(Product[Product]),'Product'[Product] = "a")
)

the one I posted is short hand for the one above. The initial filter is removed by the ALL. You can change the behaviour in at least 2 ways


summarize :=
CALCULATE (
[Total sales M],
KEEPFILTERS(Customer[Customer] = "tom"),
KEEPFILTERS('Product'[Product] = "a")
)


summarize :=
CALCULATE (
[Total sales M],
FILTER(VALUES(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(VALUES(Product[Product]),'Product'[Product] = "a")
)

2. It is possible to do OR over 2 columns by using UNION. I’m not saying it’s simple, just that it can be done.

summarize :=
CALCULATE (
[Total sales M],
DISTINCT(
UNION(FILTER(ALL(Customer[Customer]),Customer[Customer] = "tom"),
FILTER(ALL(Product[Product]),'Product'[Product] = "a")))
)

The distinct is needed because Union == Unionall in SQL

3.SUMMARIZE is effectively “expanded tables”. Filters therefore are always embedded implicity in a summarize function. You can read my (long) article here. I cover expanded tables towards the bottom. https://exceleratorbi.com.au/many-many-relationships-dax-explained/

Just following up from my previous response, well I've got a result but doesn't seem to be correct?
the table below if filtered for 'a' and tom? I've put the crossjoin in for comparison, there's also a obscured value in '****' 'b".
Richard

 

[Matt Allington]

Yeah, my bad on the UNION table. Sorry about that. You can't union 2 different tables together like this. The summarize version should work, however.

 

[dicken]

Yeah, my bad on the UNION table. Sorry about that. You can't union 2 different tables together like this. The summarize version should work, however.

Thank, for the letting me know, I spend quite bit of time trying to decide if something is not possible or if I'm doing somthing wrong, usually the latter.

RD

 

[dicken]

Yeah, my bad on the UNION table. Sorry about that. You can't union 2 different tables together like this. The summarize version should work, however.

Just to add thanks for the blog on calculated columns, I was talked out of these on EX forum quite early on but still like to know how they work.
I started using DAX studio about a week ago and find it very good for trying to understand how things work.
RD