How do conflicting row contexts work when translated into filter context through context transition? Example:
Table1=
ADDCOLUMNS (
VALUES ( 'Product'[Color] ),
"NumProductsSold", COUNTROWS (
FILTER ( ALL ( 'Product'[ProductKey] ), [Sales Amount] > 0 )
)
)
We have two row contexts when the measure is invoked, the outer one for Product[Color] and the inner one for Product[ProductKey]. They both get translated into filters through context transition and as a result we get the number of products sold of the color in the row. If however we write:
Table2=
ADDCOLUMNS (
VALUES ( 'Product'[Color] ),
"NumProductsSold", COUNTROWS (
FILTER ( ALL ( 'Product'[ProductKey], 'Product'[Color] ), [Sales Amount] > 0 )
)
)
we do get, in all rows, the total number of products sold (any color). We have two row contexts again but after context transition the inner row context generates also a filter on Product[Color]. So we have two resulting filters on Color. From what I see in the results, I deduce that the inner one overwrites the outer one. Would that be right?
Thank you
Solved! Go to Solution.
FYI: I have now had confirmation from the people at SQLBI that the filter from the innermost row context does indeed override the filter from the outer context.
Hi @AlB,
In the example you are showing the inner context is overlaping because you are using the ALL statement and this forgets all the filters and row context you have and return the result on the full data set in this case the colours and Product Key.
Check this article from SQLBI where you get an explanation on the Order of evaluation of Calculate, but the principle is the same.
Regards,
MFelix
Proud to be a Datanaut!
Thanks @MFelix for the swift reply.
I don't think what you say is completely correct. ALL() in this case does not remove any filter, it just returns the table with all the values, as explained in this article:
ALL is a table function that returns all the rows of a table or of a set of columns. This is the correct behavior of ALL whenever that result is actually required. In the very specific case of CALCULATE filters – and only in this specific case – ALL is not used to retrieve values from a table. Instead, ALL is used to remove filters from the filter context.
In fact, if we write the following:
Table3=
ADDCOLUMNS (
VALUES ( 'Product'[Color] ),
"NumProductsSold", COUNTROWS (
FILTER ( SUMMARIZE ( 'Product', 'Product'[ProductKey], 'Product'[Color] ), [Sales Amount] > 0 )
)
)
where we use SUMARIZE instead of ALL to get the same table for FILTER, the results are the same as in Table2.
Thank you
Hi @AlB,
When I say that ALL forgets the filters was a simplification of the ALL function as you can see in the last part you highlighted ALL is used to remove filters from the filter context, this is why I say it removes the filters. Just making it easier to read.
Event using ALL in other context can give you different results depending on the way the calculations are made.
But glad I could point you to the right path.
Regards,
MFelix
Proud to be a Datanaut!
Hi @MFelix
What i meant is that ALL does not really play that role here. It is not removing filters (or forgetting them as you call it) in this case. It is simply generating a table with all the values. For simplicity, let's forget about the ALL and look at the last example (Table3). There's no ALL on that one and the behaviour is exactly the same as in Table2.
Hi @AlB,
the SUMMARIZE function when applied to a table return a table based on reference table/column you are considering in the first parameter in this case the expression
SUMMARIZE ( 'Product', 'Product'[ProductKey], 'Product'[Color] )
Is picking up the Product table and taking the columns Product Key and Product Color and making a new table with all unique combinations between product key and product color. then what is done is a filter of that table considering only the ones where sales are bigger than 0.
Although the ALL expression isn't there explicit when using the summarize and then the table it is fetching all the table data.
In DAX the row context and it's transiction can be the same using different expression or different, this all depends also on the way you setup not only your measure but also your models.
Regards,
MFelix
Proud to be a Datanaut!
Let's see. I must have explained myself rather poorly.
I am not asking about SUMMARIZE, nor about ALL. They're just a means to an end.
My question remains unanswered. Let me try again:
On Table3, when we get to the invocation of the measure [SalesAmount] we have NO filter context but two row contexts, the inner one and the outer one. Then context transition takes place and we get a filter on Product[Color] and Product[ProductKey] stemming from the inner row context and a filter on Product[Color] stemming from the outer row context.
So the question is, how does DAX go about "merging" those two filters on Product[Color]?
It could do an AND or an overwrite. From the result, I deduce the inner filter overwrites the outer one but I would appreciate it if someone can explain how this actually works.
Thanks a lot
Hi @AlB,
As you say you have row context but no filter context, however what the FILTER formula on the last part of your measure is controlling the filter context for your measure in this case you are picking up your summariz table and filtering all the rows that have sales amount above 0.
Check this link on https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/ where it is discussed the row context and filter context.
On this you can see that "A filter applied on a table column affects all the rows of that table, filtering rows that satisfy that filter. If two or more filters are applied to columns in the same table, they are considered under a logical AND condition and only the rows satisfying all the filters are processed by a DAX expression in that filter context."
Proud to be a Datanaut!
Thanks. It cannot be an AND from what I see in the results. I said I deduce it's an overwrite. This is the reasoning:
Table3 has two columns, Color and NumProductsSold.
Table3:
Color NumProductsSold
Red
Black
Blue
....
Let's assume we are computing the first row of NumProductsSold. In that row, the value in column Color is Red. To compute the value, the system executes
COUNTROWS (FILTER ( SUMMARIZE ( 'Product', 'Product'[ProductKey], 'Product'[Color] ), [Sales Amount] > 0 ))
The table in FILTER, i.e., what the SUMMARIZE generates, looks like this
FilTab:
ProductKey Color
1 Black
2 Red
3 Blue
... ...
We'll call this table FilTab. FILTER starts scanning it to come up with the result. First row, system invokes
[Sales Amount]=SUM(Sales[SalesAmount])
Within the measure, the context transition takes place as explained earlier and we have, resulting from the inner row context, a filter on ProductKey=1, and a filter on Color=Black, and resulting from the outer row context, a filter on Color=Red. If we assume it's an AND as you state, Color=Black AND Color=Red is a null set, no line in the Sales table will comply with this of course as you cannot have two colors at the same time. The result of [Sales Amount] is then zero, or blank actually, regardless of whether the product with ProductKey=1 actually had any sales. Therefore, that first row of FilTab does not meet the condition and gets filtered out.
Following the same steps, we can see that the second row will not necessarily get filtered out. The inner filter is Color=Red and the outer filter is Color=Red. An AND will then give us Color=Red and [Sales Amount] will provide the sales for the product with ProductKey=2. If this is greater than zero this row stays.
We can thus see that the result of FILTER for the first row of NumProductsSold in Table3 will have all products with Color=Red that had sales. Extrapolating, the column NumProductsSold will have in each row the number of products with sales and of the color shown in that row of column Color.
Let's now assume that instead of an AND, an overwrite takes place. Let us go back to the first row of FilTab. We have filters on ProductKey=1, Color=Black from the inner row context and Color=Red from the outer row context. If the inner overwrites the outer, the result of [Sales Amount] will be greater than zero if product with ProductKey=1 has sales. In this case, this row will only be filtered out if there are no sales. With the AND, this row was always filtered out. Following on with the reasoning, we can see that NumProductsSold will have the same value in each row and that is the total number of products sold (of any color).
Do we agree so far??
Well, if we run Table3, we get in NumProductsSold exactly what the overwrite hypothesis states: the total number of products sold (any color) in every row.
I'm therefore inclined to think that an overwrite takes place. Yet with DAX appearances frequently prove to be deceptive. That's why I'd like someone to confirm the theory.
Thanks very much
FYI: I have now had confirmation from the people at SQLBI that the filter from the innermost row context does indeed override the filter from the outer context.
Find out how to participate in the first Power BI 'Can You Solve These?' challenge.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
User | Count |
---|---|
96 | |
61 | |
59 | |
55 | |
53 |