Forum Discussion

peterpan's avatar
peterpan
Icon for Helper I rankHelper I
11 months ago
Solved

Erratic Behaviour of ALL, ft. SQLBI

The official definition of ALL says it returns all values in a column or table. This leads to removal of current filter context. However, I am experiencing an erratic behaviour where it is not removing the filter context. An example of what I have experienced is in this article (Pls refer- https://www.sqlbi.com/articles/understanding-dax-auto-exist/) where Alberto was explaining about Auto-Exist and removed filters from Year but still expected it to return count of Rows in that year if selected from slicer-

# Projects = COUNTROWS ( Projects )
 
# Projects All Time = CALCULATE (
    [# Projects],
    ALL ( Projects[Year] )
)

 


I just want to understand if I'm missing something? Please help 

Need Help
Show and Tell
Tutorial Requests
  • rohit1991's avatar
    rohit1991
    11 months ago

    Hi peterpan 

    ALL(Projects[Year]) removes only the Year filter, but because of Auto-Exist, other slicer filters (like Language) still restrict the result.So the Year filter isn’t fully cleared - hence the “erratic” behaviour.
    To Fix:
    Use the full table in ALL() to remove all filters:

    # Projects All Time =
CALCULATE(
    [# Projects],
    ALL(Projects)
)

     

4 Replies

  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    Icon for Super User rankSuper User
    11 months ago

    Short answer: ALL is removing the Year filter.
    What you are seeing is Auto-Exist making it look like Year is still applied.

    When two slicers come from the same table (here: Projects[Year] and Projects[Language]), DAX computes their filters together and prunes impossible combinations. So when you select a Year, the values available/selected in the Language slicer are already restricted to the Languages that exist in that Year. Inside your measure you do:

    # Projects All Time :=
    CALCULATE ( [# Projects], ALL( Projects[Year] ) )

     

    ALL(Projects[Year]) removes the Year filter from the filter context, but it does not change the fact that the Language filter itself was produced under the Year constraint (the pruned set). Languages that only exist in other years were never part of the filter, so removing Year cannot bring them back. That’s Auto-Exist.

     

    How to get the expected behaviour

    Use a star schema with slicers coming from separate dimension tables:

    • DimDate[Year] → Year slicer
    • DimLanguage[Language] → Language slicer
    • Projects fact table related to both

    Then:

    # Projects := COUNTROWS(Projects)

    # Projects All Time :=
    CALCULATE ( [# Projects], REMOVEFILTERS( DimDate[Year] ) ) -- or ALL(DimDate[Year])

     

    Now the Year filter is truly ignored while the Language filter remains intact, because they come from different tables and Auto-Exist no longer trims one by the other.

    If you can’t remodel

    Create a disconnected Language slicer table and apply it with TREATAS:

     

    # Projects All Time (disconnected lang) :=
    VAR Lang = VALUES( 'Lang Slicer'[Language] )
    RETURN
    CALCULATE (
    [# Projects],
    TREATAS ( Lang, Projects[Language] ),
    REMOVEFILTERS ( Projects[Year] )
    )

     

    So nothing is “wrong” with ALL—it’s Auto-Exist interacting with slicers from the same table.

    I hope it helps.

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    11 months ago

    Hi peterpan 

    ALL(Projects[Year]) removes only the Year filter, but because of Auto-Exist, other slicer filters (like Language) still restrict the result.So the Year filter isn’t fully cleared - hence the “erratic” behaviour.
    To Fix:
    Use the full table in ALL() to remove all filters:

    # Projects All Time =
CALCULATE(
    [# Projects],
    ALL(Projects)
)

     

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    11 months ago

    Thankyou, rohit1991 and Ilgar_Zarbali for your responses.

    Hi peterpan,

    We appreciate your inquiry through the Microsoft Fabric Community Forum.

    We would like to inquire whether have you got the chance to check the solutions provided by rohit1991 and Ilgar_Zarbalito resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi peterpan,

    We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

    Thank you.