Counting rows in a table based on subtable result

Question

Sample data:

--------------------------------------------------
| Customer          | Value                      | 
--------------------------------------------------
| A                 | 5                          |
--------------------------------------------------
| B                 | 10                         |
--------------------------------------------------
| B                 | -10                        |
--------------------------------------------------
| C                 | 10                         |
--------------------------------------------------
| C                 | 5                          |
--------------------------------------------------

What I want to do is to count the distinct number of customers where the sum of values is not 0.

Currently I'm using DISTINCTCOUNT(Customer) which provides 3 (A,B,C) whereas the intended output is 2 (A,C).

Edit: As this is tagged both Power BI and DAX, I want to stress that I'm looking for DAX solutions, not SQL. As such, I've reverted the change to the table as I don't believe the SQL expression shows my data as accurately.

score 2 · Answer 1 · answered Jan 10 '19 at 04:14

2

select count(customer) from
(
    select customer from table
    group by customer
    having sum(value)<>0
) X

answered Jan 10 '19 at 04:14

LoztInSpace

923
4
8

score 0 · Answer 2 · answered Mar 05 '19 at 14:21

0

Assuming you have a table named "TEST"

EVALUATE
FILTER (
    SUMMARIZE ( TEST, TEST[Customer], "SumStuff", SUM ( TEST[Value] ) ),
    [SumStuff] <> 0
)

answered Mar 05 '19 at 14:21

RoyceBautista

151
1
3

Counting rows in a table based on subtable result

2 Answers2