problem with all except

[ahmedshalabyy12]

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 ) )

[FBergamaschi]

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

[Ahmed-Elfeel · Accepted solution]

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.