ALLSELECTED Shadow Filters

[ahmeddaffaie80]

Hi Experts,

I have a question!!

I was reading through "The Definite Guide to DAX" by Russo and Ferrari, and came across how ALLSELECTED works (on page 652). I noticed Marco and Alberto come up with a different visual query and they started to explain the execution plan. Their explanation doesn't make any sense!!

To me, ALLSELECTED works just fine because SUMMARIZECOLUMNS of visual query creates one big filter that overrides any inclusive measures with ALL* functions inside (i.e. if you selected few values from a slicer, this context will be of high priority and overrides any incoming filter context later).

It doesn't need any shadow filters or special cases.

Does anyone agree with me or the book is coherent about it? (this picture might give some cleanace)

Thank you for answering me,

Ahmed 

(Update)

I've just noticed that the result of CALCULATETABLE (A,B) depends on the A and B expression where if any expression contains ALL* will be dominant over the other, else if A and B from different columns or table, the result is the intersection between them.

[Mrxiang]

You are correct that ALLSELECTED will work as expected in DAX if you have a slicer that includes all of the values that you want to include in the calculation.  In this case, SUMMARIZECOLUMNS will create a filter that includes all of the values, and the ALL* functions will be applied to that filter.
However, it is possible that there are other scenarios where ALLSELECTED may not produce the expected results.  For example, if you have a slicer that includes a few specific values and then a filter that includes all of the values, then ALLSELECTED may not produce the expected results because the filter will take precedence over the slicer.
It is also possible that there are other scenarios where the behavior of ALLSELECTED can be different, depending on the specific implementation of DAX in your version of Excel.
In general, it is a good idea to test your calculations and see how they behave in different scenarios to ensure that they produce the expected results.

[ahmeddaffaie80]

@Mrxiang  You are 100% correct but can you tell where the shadow filter residues in this situation?
All I see is a combination of ALL* and SUMMARIZECOLUMNS functions. 

Shadow filter might pop up when we have an iterator like ADDCOLUMNS which we don't have any how

[v-stephen-msft]

Hi @ahmeddaffaie80 ,

It's great that you are reading "The Definitive Guide to DAX" by Russo and Ferrari. They are experts in the field and their book is a great resource for learning DAX.

Regarding your question about ALLSELECTED, it is true that the SUMMARIZECOLUMNS function creates a filter context that overrides any incoming filter context later. This means that if you select a few values from a slicer, this context will be of high priority and will override any incoming filter context later.

However, the behavior of ALLSELECTED can be affected by other factors, such as shadow filters or special cases. It is possible that the book is referring to these factors when explaining the execution plan.

Regarding your observation about CALCULATETABLE, it is correct that the result of CALCULATETABLE depends on the expressions in A and B. If any expression contains an ALL* function, it will be dominant over the other expressions. If A and B are from different columns or tables, the result is the intersection between them.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[ahmeddaffaie80]

Thank you so much for taking time to reply to my question.
No offense! I feel there is a language barrier when I read through 'Definite Guide to DAX' as English is not the first language of the author. However, I am not denying the fact they are the Gods of DAX!! 

Moreover, SUMMARIZECOLUMNS recently been enriched with a new context transistion functionality so it can be 2nd argument of CALCULATE but it is still not fully understood

[ahmeddaffaie80]

Apparantly, there is none on the earth knows how the shadow filter works except SQLBI guys who kept it vague to us 😞😞😞