Forum Discussion

ahmedshalabyy12's avatar
ahmedshalabyy12
Helper V
10 months ago
Solved

problem with all except

Dears,

 

I’m trying to display the total amount (8M) for each organization in every row of the matrix, but it’s not working as expected.

  • Org Name comes from Dim_Organizations

  • Recommended Amount comes from Fact_Table

  • Terms come from Dim_Terms

  • Rec Amount is supposed to display 8M in each row, but instead it’s splitting the value.

Can you please help me identify why this is happening and how to fix it?


Measure
Rec_Amount =
CALCULATE (
[Total Amount (Sum)],
ALLEXCEPT ( Organizations, Organizations[Organization Name] )
, ALLEXCEPT(Terms,Terms_years ) )

 

Need Help
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    10 months ago

    Hi ahmedshalabyy12,

     

    The issue here that is you are applying this to two different tables which is causing unexpected behavior

     

    Your current measure:

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    ALLEXCEPT ( Organizations, Organizations[Organization Name] ),
    ALLEXCEPT ( Terms, Terms[TermYears] )
)

    This is saying "Remove all filters except Organization Name from the Organizations table AND remove all filters except TermYears from the Terms table (This creates conflicting filter contexts)

     

    You need to use ALLEXCEPT on the fact table (or use a different approach) to ignore the TermYears filter while keeping the Organization Name filter

     

    So you can use ALLEXCEPT on the fact table (if possible)

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    ALLEXCEPT ( Fact_Table, Organizations[Organization Name] )
)

     

    Or use REMOVEFILTERS for more precise control

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    Organizations[Organization Name] = SELECTEDVALUE(Organizations[Organization Name]),
    REMOVEFILTERS ( Terms )
)

     

     

    You can also ALL with VALUES (most reliable)

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    ALL ( Terms ),
    VALUES ( Organizations[Organization Name] )
)

     

    This will:

    • Remove any filtering from the Terms table (including TermYears)

    • Preserve the current Organization Name filter

    • Show the total $8M for (Black Futures Lab) in every row of your matrix

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

2 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    10 months ago

    Hi ahmedshalabyy12,

     

    The issue here that is you are applying this to two different tables which is causing unexpected behavior

     

    Your current measure:

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    ALLEXCEPT ( Organizations, Organizations[Organization Name] ),
    ALLEXCEPT ( Terms, Terms[TermYears] )
)

    This is saying "Remove all filters except Organization Name from the Organizations table AND remove all filters except TermYears from the Terms table (This creates conflicting filter contexts)

     

    You need to use ALLEXCEPT on the fact table (or use a different approach) to ignore the TermYears filter while keeping the Organization Name filter

     

    So you can use ALLEXCEPT on the fact table (if possible)

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    ALLEXCEPT ( Fact_Table, Organizations[Organization Name] )
)

     

    Or use REMOVEFILTERS for more precise control

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    Organizations[Organization Name] = SELECTEDVALUE(Organizations[Organization Name]),
    REMOVEFILTERS ( Terms )
)

     

     

    You can also ALL with VALUES (most reliable)

    Rec_Amount =
CALCULATE (
    [Total Amount (Sum)],
    ALL ( Terms ),
    VALUES ( Organizations[Organization Name] )
)

     

    This will:

    • Remove any filtering from the Terms table (including TermYears)

    • Preserve the current Organization Name filter

    • Show the total $8M for (Black Futures Lab) in every row of your matrix

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • FBergamaschi's avatar
    FBergamaschi
    Super User
    10 months ago

    The problem is that you are not removing the filter nor from Terms nor from Organization name

     

    If you want to see 8M everywhere, you need to remove those filters, so please use this code (or a slight variation we might fix later)

     

    Rec_Amount =
    CALCULATE (
          [Total Amount (Sum)],
          REMOVEFILTERS( Organizations[Organization Name] ),
          REMOVEFILTERS( Terms_years )
    )

     

    If you want to remove filters from other columns, from those tables, you can list them in the rremovefilters call afer a comma

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI