CountX with filtered measures and dynamic slicers

[mcomsto]

Hi,  I have spent way to much time trying to figure this one out and it seems like it should be easy, but I'm no expert.  I am trying to apply a countx measure with a filter, but it is giving me the wrong count.  I have attach a mock up pbix file.  If you can provide an insight, it would be greatly appreciated.

The measures in question are the Q1 Above Zero and the Q1 Equals Zero.

https://www.dropbox.com/s/gbfb8nmwyp2bkrp/mockupforcountxissue.pbix?dl=0

[edhans · Accepted solution]

I don't think the measure is really the problem, it is the underlying data. Thanks for the PBIX file - very helpful.

8 is the right answer, because COUNTX is looking at the rows in the actual table, and there are 8 that are zero.

The below will tell you the unique count of customers that are zero, but it still isn't showing you what is in your summary table. It just eliminates the duplicate customer numbers.

Q1 Equals Zero = 
CALCULATE(
    COUNTROWS(
        DISTINCT(Sheet1[Customer])
    ),
    FILTER(
        Sheet1,
        [Comp Q1 Form Score Diff] = 0
    )
)

  I think what you want is this, which returns 2, the number of 0's in the Q1 column:

Customer Totals are Zero =
COUNTROWS(
    FILTER(
        SUMMARIZECOLUMNS(
            Sheet1[Customer],
            "Total", SUM( Sheet1[AllData.Q1 Score] )
        ),
        [Total] = 0
    )
)