All Function Not Working as expected !!!

[asif999]

I’m trying to calculate the distinct count of 'Live Order Status' for each OID, and I want to ignore any filters coming from the 'Live Order Status' and 'Supplier SKU' columns in the matrix
and keeping the filters from the OID column






Here’s the DAX measure I used:

Multi Status =
CALCULATE(
    DISTINCTCOUNT(
        Orders[Live Order Status]
    ),
    ALL(
        Orders[Live Order Status],
        Orders[Supplier SKU]
    )
)

But this is not giving the expected result. It looks like some filters from the visual or other related tables are still being applied.

When I use ALLEXCEPT() instead of ALL(), it works fine.

I’ve also shared a screenshot of my table relationships. Can you help me understand why ALL() is not working as expected here?

[DataNinja777 · Accepted solution]

Hi @asif999 ,

The issue occurs because you're using ALL() to remove filters from the Live Order Status and Supplier SKU columns, but those columns are part of the visual itself. In Power BI, when a matrix includes columns like Live Order Status, each row inherently carries that filter context even if you try to remove it using ALL(). The visual engine still breaks down the measure row-by-row, so each evaluation sees just one status and counts it as 1. This is why the measure behaves correctly only when the matrix is collapsed to OID, and fails when expanded.

By using ALLEXCEPT(), you’re explicitly saying: “keep only the OID context, wipe everything else,” which aligns perfectly with your goal of calculating the distinct count of statuses per OID regardless of the status or SKU being shown in the matrix. Here's the correct version of the DAX formula:

Multi Status =
CALCULATE(
    DISTINCTCOUNT(Orders[Live Order Status]),
    ALLEXCEPT(Orders, Orders[OID])
)

This measure will always return the correct distinct count of statuses for each OID, ignoring the extra breakdowns in the visual. It works because it respects only the OID-level granularity and clears out all other visual filters, even if they are actively part of the matrix layout.

Best regards,

[v-dineshya · Accepted solution]

Hi @asif999 ,

Thank you for reaching out to the Microsoft Community Forum.

ALL() removes filters only from [Live Order Status] and [Supplier SKU], but leaves all other filters (including the matrix structure and any filters from related tables) untouched.

ALLEXCEPT() Keeps only the filter on OID, and removes all other filters on the Orders table, including those implicitly coming from related matrix rows like [Live Order Status] and [Supplier SKU]. That’s why it behaves as expected because it guarantees that you're working within the context of a specific OID, regardless of any other matrix row values.

Multi Status =
CALCULATE(
DISTINCTCOUNT(Orders[Live Order Status]),
ALLEXCEPT(Orders, Orders[OID])
)

It Ignore all filters except for the OID.

Note: If needed, you can also use REMOVEFILTERS() or KEEPFILTERS() for more granular control:

Multi Status =
CALCULATE(
DISTINCTCOUNT(Orders[Live Order Status]),
REMOVEFILTERS(Orders[Live Order Status], Orders[Supplier SKU])
)
This is functionally close to ALL(), but often behaves better in visuals.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

[DataNinja777 · Accepted solution]

Hi @asif999 ,

The issue occurs because you're using ALL() to remove filters from the Live Order Status and Supplier SKU columns, but those columns are part of the visual itself. In Power BI, when a matrix includes columns like Live Order Status, each row inherently carries that filter context even if you try to remove it using ALL(). The visual engine still breaks down the measure row-by-row, so each evaluation sees just one status and counts it as 1. This is why the measure behaves correctly only when the matrix is collapsed to OID, and fails when expanded.

By using ALLEXCEPT(), you’re explicitly saying: “keep only the OID context, wipe everything else,” which aligns perfectly with your goal of calculating the distinct count of statuses per OID regardless of the status or SKU being shown in the matrix. Here's the correct version of the DAX formula:

Multi Status =
CALCULATE(
    DISTINCTCOUNT(Orders[Live Order Status]),
    ALLEXCEPT(Orders, Orders[OID])
)

This measure will always return the correct distinct count of statuses for each OID, ignoring the extra breakdowns in the visual. It works because it respects only the OID-level granularity and clears out all other visual filters, even if they are actively part of the matrix layout.

Best regards,