Remaining Stores Not Delivered CALCULATE FILTER

[Darrell]

Thanks for viewing.  What I thought would be simple has me stumped.

PBI File: PBI Deliveries File 

I have a simple task - find out what stores were NOT delivered depending on the user's Week Ending choice.  It seems to work choosing 1 week, but when I choose multiple weeks, using the slicer, the number should decrease and not increase.

The delivered column contains a whole number of how many deliveries per week were made (0-3).  I need to focus solely on those stores that didn't have a delivery over the weeks - stores that 'delivered' data adds up to 0.

3 Columns of Data

 The "visual" filter I have in place to show only those stores not delivered:

When I choose 1 week, it works fine.  The total is 208 in this example:

count_not_delivered = 
    CALCULATE(
        COUNTA(deliver_data[store_num]), 
        FILTER('deliver_data', 'deliver_data'[delivered] = 0)
    )

When I choose all 3 dates, it should only be 13, but instead, it adds up to 643. 

I understand why - it's counting all stores with a 0 in the 'delivered' column for all 3 weeks individually.  I need it to somehow SUM all 3 weeks and filter by that and I'm not finding anything online to help.

I feel like this should work, but of course, it doesn't.

count_not_delivered = 
    CALCULATE(
        COUNTA(deliver_data[store_num]), 
        FILTER('deliver_data', SUM('deliver_data'[delivered]) = 0)
    )

Any help would be appreciated!

[edhans · Accepted solution]

This will do it @Darrell 

New Measure = 
COUNTX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'deliver_data',
                'deliver_data'[store_num]
            ),
            "@Total",
                CALCULATE(
                    SUM( 'deliver_data'[delivered] )
                )
        ),
        [@Total] = 0
    ),
    'deliver_data'[store_num]
)

[Darrell]

Thanks!  I'll take some time and work through your solution.  I appreciate it!

[edhans]

Glad to help @Darrell - what ADDCOLUMNS(SUMMARIZE()) is doing is building a mini-table that is grouped the way you were describing the desired results, the it FILTER()'s it, then COUNTX will count the instances. Any questions, let me know. 

I used DAX Studio to build the mini-table so I could see what it was. Here is how the table looks with no filtering by a slicer. Once in Power BI and a slicer affects it, it changes.