2

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.

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

Assuming you have a table named "TEST"

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.