Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ahmeddaffaie80
Frequent Visitor

ALLSELECTED Shadow Filters

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.2023-05-18 10_19_51-f4de179lvg0b1.png (1409×425).png

5 REPLIES 5
Mrxiang
Helper II
Helper II

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.

@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
Community Support
Community Support

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.           

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
Frequent Visitor

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors