如何通过DAX查询统计无待办状态的完全已完成门店？

Got it, let's break down what's wrong with your current DAX and how to fix it.

The Problem with Your Current Formula #

Your existing code:

CALCULATE(DISTINCTCOUNT(OUTBOUND[Store]),FILTER(OUTBOUND,[Complete Status] = 1 && [Pending Status]= 0))

works at the row level—it filters individual rows where the status is complete and not pending, then counts the distinct stores associated with those rows. The issue? If a store has some rows that meet this condition but also has any pending rows, it still gets counted. That's why you're seeing semi-completed stores in your results.

You need to filter at the store level instead: check that a store has no pending rows whatsoever, not just that it has some completed rows.

Corrected DAX Queries #

Here are two versions depending on your exact needs:

1. Count Stores with No Pending Status (All Rows Are Non-Pending)

If you just need stores that have zero pending rows (adjust if you don’t need to enforce full completion):

CALCULATE(
    DISTINCTCOUNT(OUTBOUND[Store]),
    FILTER(
        // Get a list of all unique stores
        VALUES(OUTBOUND[Store]),
        // Check if the store has 0 rows with pending status
        CALCULATE(COUNTROWS(OUTBOUND), OUTBOUND[Pending Status] = 1) = 0
    )
)

2. Count Fully Completed Stores (No Pending + All Rows Are Completed)

If you need to ensure the store is fully completed (all rows are completed AND no pending rows), use this:

CALCULATE(
    DISTINCTCOUNT(OUTBOUND[Store]),
    FILTER(
        VALUES(OUTBOUND[Store]),
        // No pending rows (max pending status is 0, meaning all are 0)
        MAX(OUTBOUND[Pending Status]) = 0
        // All rows are completed (min complete status is 1, meaning no rows are 0)
        && MIN(OUTBOUND[Complete Status]) = 1
    )
)

This works assuming [Pending Status] and [Complete Status] use 0/1 values:

• MAX(OUTBOUND[Pending Status]) = 0 confirms there are no rows with pending status (even one pending row would make the max value 1)
• MIN(OUTBOUND[Complete Status]) = 1 confirms every row is completed (even one incomplete row would make the min value 0)

Why This Works #

By wrapping the store list in FILTER(VALUES(OUTBOUND[Store]), ...), we're evaluating each store as a whole. The inner CALCULATE or aggregation functions (MAX/MIN) run in the context of each individual store, so we can check the status of all rows for that store—not just individual rows.

内容的提问来源于stack exchange，提问作者Nishantha Maduranga