What is in reality a DAX filter context

What is in reality a DAX filter context

1. #SQLSAT777
What is in reality a DAX filter
context
Marco Pozzan

2. #SQLSAT777
Organizers
GetLatestVersion.it

3. #SQLSAT777
Sponsors

4. #SQLSAT777
Who am I? (Marco Pozzan)
Businessintelligenceconsultantat www.methode.it
TeachingattheUniversityofPordenoneinthecourseofdataanalysisandBigData
CommunityLeadof1nn0va(www.innovazionefvg.net)
MCP,MCSA,MCSE,MCTandsince2014MVPandsince2016MVPReconnectfor
MicrosoftDataPlatformandspeakerinseveralconferencesonthetopic
[email protected]
@marcopozzan.it
www.marcopozzan.it
http://www.scoop.it/u/marco-pozzan
http://paper.li/marcopozzan/1422524394

5. #SQLSAT777
Let’s start

6. #SQLSAT777
Agenda
Recap filter context
Base Tables vs Expanded tables
Filter context in deep
•Filter context propagation
•Filter context operators
Arbitrary shaped set
Understanding what ALLSELECTED does

7. #SQLSAT777
Recap filter context
•Defined by the context introduced by a pivot
table orvisual
•Rows outside of the filter context are not
considered for the computation
•Can be created with specific functions too

8. #SQLSAT777
Base table
vs
Expanded table

9. #SQLSAT777
Base table and Expanded table
•A base table is a standard
table in the model
•Productand Subcategory
are base tables
•The relationship between
two tables is LEFT OUTER
JOIN

10. #SQLSAT777
Base table vs Expanded table
•An expanded table of
productis a table that have
all subcategorycolumn
beside the product column
•Following the left outer join
relationship
•For each base table there
is an expanded version

11. #SQLSAT777
Base table vs Expanded table
Each Table = all of its columns (native columns)
+
all of the columns in all related tables
(related columns)
This theory does not happen physically inside the
engine but help us understanding how filters
context work

12. #SQLSAT777
The RELATED function
Do you remember the RELATEDfunction?
is able to follow a chain of relationship and retrive
the value of a column in a RELATED table. It’s right?
(not true ☺)
•Doesn’t follow relationships but it
simply grants you access to the
related columns (expanded table)

13. #SQLSAT777
What happens with single relation?
•Sales exapanded table contains
the ALL column of the ALL model ☺
•Product expanded table contains
all Product columns + all
Subcategory columns
•Calendarand SubCategory
expanded table are equal to base
table

14. #SQLSAT777
What happens with bidirectional?
•Sales exapanded table contains ALL the
columns of the ALL model ☺
•Product expanded table contains all
product product + all column subcategory
•SubCategory expanded table is equal to
base table
•!!!Calendar has not the expanded table
but through the injection of filtering code in
the DAX calendar is able to filter Sales. And
Sales is able to filter Calendar

15. #SQLSAT777
Filter context
in deep

16. #SQLSAT777
Filter context propagation: Rule 1
•The filter set on Product[Name] = “classic Vest, S”(native
column)
•I will filter Sales because Sales contains product[name] as an
expanded column (Sales Expanded)
So filtering an individual column means filtering all the tables
that contain that columneither as native column or as a releted
column.

17. #SQLSAT777
Filter context propagation: Rule 2
The filter context propagates only from 1 side
to Many side. It’s right?
(not really true ☺)
•Means placing a filter on all column of the product table
•Product table is being filtered as a whole and filtering the
product table. But I am really filtering the expanded table of
product (all column that belong to a SubCategory) and i
am really filtering SubCategory
I am moving the filter from the many to the one side!!! ☺

18. #SQLSAT777
Filter context propagation: Rule 3
When filtering by using the Product table, the
Subcategory table is really filtered. Does the same
happens when you filter by one column of Product ?
If i filter an individual column of product then I’m
not filtering subcategory,thereis a big different
between filtering a table and filtering a
column(work on the base table)

19. #SQLSAT777
Recap: filter context propagation
Column filter
•Work on the base table
•Does not refer to related column
Table Filter
•Work on the expanded table
•Always includes related columns
Table interactions
•When the same column (native o related) is
referred by different contexts last filter
wins (example nested calculate)
•Ex. “Classic Vest, S” wins

20. #SQLSAT777
Year MonthName
2013 December
2014 January
Filter context and operators
•A filter context is based on tuple
•Tupleis a given value for one set of columns
•A filteris a set of tuple
•A filter context is a set of filters
Year MonthName
2013 December
2014 January
Tupla
Year MonthName
2013 December
2014 January
Name
Classic Vest, S
AWC Logo Cap
Tupla
Name
Classic Vest, S
AWC Logo Cap

21. #SQLSAT777
Filter context and operators
•A filter context is a set of filters
Year MonthName
2013 April
2012 May
Name
Classic Vest, S
AWC Logo Cap
Filter 1
Filter 2

22. #SQLSAT777
Filter context and operators
There are three basic operators that DAX uses to mix different filter
context
•Intersect
•Allow to performs intersection
•Overwrite
•Allow to overwrite a filter with a new filter
•Remove
•Remove a filter from a existing filter

23. #SQLSAT777
Name
ClassicVest, S
Filter context and operators: Intersect
You have 2 filter context, and
you want to intersect them in
a new filter context (that is to
put them in what we called a
logical AND)
Intersect is the operator that
CALCULATE uses when it needs
to merge together filters that
you create as a separate filter
parameter in the statement.
Year
2013
Name
ClassicVest, S
Year
2013

24. #SQLSAT777
Filter context and operators: Overwrite
is used by CALCULATE not when it
needs to merge different filters in
thesame column, but when you have
nested CALCULATE.
1.Removeany reference for Namefrom
externalfilter (AWC Logo cap)
2.Intersectwith the new filter context from
internalfilter)
if it tries to use intersect it would
create an empty set
Name
AWC Logo Cap
Name
ClassicVest, S
Name
ClassicVest, S
“Classic Vest, S”
overwrites “AWC
Logo Cap”
Name
11
2

25. #SQLSAT777
Filter context and operators: Remove
ALL return all the column values when used
as a top level function in CALCULATE. It’s
right?
(Not true )
In a CALCULATE filter ALL doesn’t use ALLbut applies REMOVE
•Remove the filters from all the columns contained in the table it returns
•REMOVEoperator is the operator used by ALL when it used as a top-level
function inside CALCULATE

26. #SQLSAT777
The new way: using ALL product[Name]
doesn’t mean returning all the Product
it means remove the filter from the product
Filter context and operators:Removeexample
Old way of thinking about ALL in
CALCULATE
Name
chain
blade
steam
...
Name
chain
blade
steam
…..
Name
AWC Logo
Cap
Name
AWC Logo Cap
Empty
Filter
Result
Result
Name

27. #SQLSAT777
Why the operator is important
The equivalent DAX formula
The result is the same.
It’s right?
606 (All Products)
158 (All
Products
sold)

28. #SQLSAT777
Recap Operator
A and B are two tables
•INTERSECT (A intersect B)
•Puts the filter in AND
•OVERWRITE (A overwrite B)
•Replace filter in B
•This operator not exist in reality but use REMOVE + INTERSECT
•REMOVE
•Remove columns from the filter context
•Used by ALL in a CALCULATE when it used as a top-level function

29. #SQLSAT777
Arbitrary
shaped set

30. #SQLSAT777
Simple Filter
Imagine that you have this filter context
This filter is simplified as the intersection of single-column filter
if any filter can be simplified as the intersection of the individual column
filters then we call it a simple filter (filter equivalency)
Year MonthName
2013 April
2012 May
Year
2013
2012
MonthName
April
May

31. #SQLSAT777
Arbitrarily shaped set by UI
•simple filters are good because they don’t create any problem at all
•there are also filters that cannot be simplified
this filter cannot be simplified. You cannot express this filter as
the intersection of the individual column filters.
Year MonthName
2013 April
2012 May
Year
2013
2012
MonthName
April
May
This arbitrarily shaped cannot be created as one
filter on the year and another one on the month!!!

32. #SQLSAT777
Problem with Arbitrarily shaped set
Measure than compute the average of sales at the monthgranularity
Why is the Total wrong? In the next slide you will see what happens
is the interaction between
arbitrarily shaped sets
and context transition and
the operatorsin calculate

33. #SQLSAT777
Problem with Arbitrarily shaped set
1. In Totalthere is an iteration. For each
month we compute the monthly sales.
2.During 1 iteration the selected month is April and the original filter
context contain 2013 April, 2012 May.
Calculatethrough context
transitionwill use overwrite
that removeMonthNameby
Original Filter context
3.Each iteration is not computing the sales
of a single month but the sales of 2 year
together, and the average was much higher:
OVERWRITE destroy arbitrarily shape set
Year MonthName
2013 April
2012 May
MonthName
April
MonthName
April
Year
2013
2012

34. #SQLSAT777
Year MonthName
2013 April
2012 May
Year MonthName
2013 April
2012 May
Solution with Arbitrarily shaped set
KEEPFILTER modify the behaviour of CALCULATE when the context
transition happening during iterations
if there is a context transition and invoke CALCULATE, don’t use
OVERWRITE, instead use INTERSECT(used as a top level function)
KEEPFILTER
preserve
arbitrarily shape set!!!
MonthName
April
MonthName
April

35. #SQLSAT777
Be careful to the SUMMARIZE
•Is the only function in DAX that create at the same time a row context
and filter context and both are working togheter
•The reason why sometime SUMMARIZE
returns strange values it’s:
•the row context created by SUMMARIZE will be subject to context transaction
whenever you call a measure from inside your code
• And context transition use removeand is able to destroy arbitrarily
sets (model,color)
Row context on
Test[Color]
Filter context on
Test[Color]
ModelColor
Bike Blue
Model Color
Bike Green
shirt Green
Model Color
Bike Red
shirt Red
ModelColor
Bike
Model Color
Bike
shirt
Model Color
Bike
shirt

36. #SQLSAT777
Recap: Arbitrarily shaped set
•not use SUMMARIZE to compute anything!!!
•Use it to do a grouping by
•But whenever you want to compute some values use ADDCOLUMNS
instead.
•ADDCOLUMNS is not subject to this behavior ☺
•KEEPFILTER preserve arbitrary shaped set

37. #SQLSAT777
Understanding
what
ALLSELECTED does

38. #SQLSAT777
Understanding what ALLSELECTED does
ALLSELECTED Obtain visual totals (that is the total that the user is
looking at) in queries.
•Removes filters context from columnsand rowsin the current query,
•Retaining all other filters context
•Retaining explicit filters context (when use CALCULATE)
It’s true if and only if no
iteration is happening!!!!

39. #SQLSAT777
Understanding shadow filter context
•Special filter context created by iterators which is always inactive
except for certain situations
•The DAX above generates a shadow filter context that contains
the values of Product[Color] in the current filter context or
explicit filter context when iterator starts. (It is inactive)
•The shadow filter context is activated by ALLSELECTED!!!

40. #SQLSAT777
ALLSELECTED(Column) is a table function
•no shadow context to activate (no previous shadow context) because ALLSELECTED is
contained in CONCATENATEX that creates a shadow contex
•ALLSELECTED works on filter context but when a column is used as parameter it ignores
the cross filters. The result is that Product[Product] contains all values.
Brand Color Product
Contoso Red Bike
Contoso Green Helmet
Contoso Blue Shoes
Tailspin ToysRed Robot
Tailspin ToysGreen Shirt
Tailspin ToysBlue Rollerblades
Fabrikam Red Motorbike
Fabrikam Green Keyboard
Fabrikam Blue Piano

41. #SQLSAT777
ALLSELECTED(Column) is a table function
•ADDCOLUMNS creates a shadow context (shadow context 1), which is the result of VALUES()
in the defined filter context when iterator starts ("Contoso","Fabrikam" )
•CONCATENATEX creates another shadow context(shadow context 2) and ALLSELECTED
activates and uses the last shadow context, which is shadow context 1
Brand
Contoso
Fabrikam
Brand Color
Contoso Red
Contoso Green
Contoso Blue
Tailspin ToysRed
Tailspin ToysGreen
Tailspin ToysBlue
Fabrikam Red
Fabrikam Green
Fabrikam Blue

42. #SQLSAT777
ALLSELECTED(Table) is a table function
•No shadow context to activate (no previous shadow context), ALLSELECTEDis contained
in CONCATENATEX which creates a shadow context (that remains unactive)
•When a table is the parameter the cross filters are considered as opposedto when a
column is used as a parameter
Brand ColorProduct
Contoso Red Bike
Contoso GreenHelmet
Contoso Blue Shoes
Tailspin ToysRed Robot
Tailspin ToysGreenShirt
Tailspin ToysBlue Rollerblades
Fabrikam Red Motorbike
Fabrikam GreenKeyboard
Fabrikam Blue Piano

43. #SQLSAT777
ALLSELECTED(Table) is a table function
•ADDCOLUMNS creates a shadow context withALL(Product[Color]) (shadow Context 1)
•CONCATENATEX creates another shadow context(shadow context 2) and ALLSELECTED
activates and uses the last shadow context, which is shadow context 1for [Color], plus
the original filter on Brands
Brand ColorProduct
Contoso Red Bike
Contoso GreenHelmet
Contoso Blue Shoes
Tailspin ToysRed Robot
Tailspin ToysGreenShirt
Tailspin ToysBlue Rollerblades
Fabrikam Red Motorbike
Fabrikam GreenKeyboard
Fabrikam Blue Piano

44. #SQLSAT777
Brand ColorProductQuantity
Contoso Red Bike 10
Contoso GreenHelmet 20
Contoso BlueShoes 30
Tailspin
Toys
Red Robot 40
Tailspin
Toys
GreenShirt 50
Tailspin
Toys
Blue
Rollerbla
des
60
Fabrikam Red
Motorbi
ke
70
Fabrikam Green
Keyboar
d
80
Fabrikam BluePiano 90
ALLSELECTED() is a CALCULATE modifier
•No shadow context to activate (no previous shadow context), ALLSELECTED
performs ALLSELECTED on all columns, the filter context is used.

45. #SQLSAT777
ALLSELECTED() is a CALCULATE modifier
•Using ALLSELECTED as a CALCULATE filter argument means transforming ashadow
filter context into an explicit filter context
•ALLSELECTED activates and uses a shadow filter context that contains ALL [Brand]
and this is the new explicit filter context, SUM is computed on all brand (60 +
150 + 210 = 450) and then this computation is repeated by SUMX 3 times (one
for each brand)
Brand Color Product Quantity
Contoso Red Bike 10
Contoso Green Helmet 20
Contoso Blue Shoes 30
Tailspin Toys Red Robot 40
Tailspin Toys Green Shirt 50
Tailspin Toys Blue Rollerblades60
Fabrikam Red Motorbike 70
Fabrikam Green Keyboard 80
Fabrikam Blue Piano 90

46. #SQLSAT777
Recap ALLSELECTED
ALLSELECTED(<Table>)
•Appliesthe last available
shadow filter context,
on anycolumnthathas
one
•In absenceof shadow
context the defined
(normal) filter contextis
used
•Cross filtersare
considered
ALLSELECTED(<Column>)
•Appliesthe last available
shadow filter context
on the givencolumn
•In absenceof shadow
context to apply, allthe
columnvaluesare
returned
•Cross filtersarenot
considered
ALLSELECTED()
•Behavesasifallthe
columnsare usedas
parameter
•Appliesthe last available
shadow filter context on
allthe columns
•In absenceof shadow
context to apply, allthe
columnvaluesare
returned
•Cross filtersare not
considered

47. #SQLSAT777
References
•https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/
•https://www.sqlbi.com/blog/marco/2015/08/05/the-allselected-
function-under-the-cover-dax-tabular-powerpivot-powerbi/
•https://www.sqlbi.com/articles/understanding-allselected/

48. #SQLSAT777
Thanks!