topic Re: Remaining Stores Not Delivered CALCULATE FILTER in DAX Commands and Tips

Remaining Stores Not Delivered CALCULATE FILTER

Darrell — Thu, 05 Aug 2021 13:21:58 GMT

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

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!

Re: Remaining Stores Not Delivered CALCULATE FILTER

edhans — Thu, 05 Aug 2021 14:43:25 GMT

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

Re: Remaining Stores Not Delivered CALCULATE FILTER

Darrell — Thu, 05 Aug 2021 15:20:47 GMT

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

Re: Remaining Stores Not Delivered CALCULATE FILTER

edhans — Thu, 05 Aug 2021 15:34:24 GMT

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.