Aggr Count Distinct function

[da_pagnott]

Hi guys,

I'm trying to use count distinct function to check how many ID has a total equal to 0 (please see the sample below), but I can't just use count distinct funzion because the format of the input data has more than one row for each ID:

|-----ID-----|     |-----Outcome/Income-----|

001                    +300

001                    0

001                     -150

002                   0

002                   0

003                  +350

003                 -200

I need to count how many IDs has a total Outcome/Income = 0 (in this case just one, the ID 002), but if I use just count distinct for ID with the condition Outcome/Income=0 i get 2 as result (ID 001; 002).

How can I proceed?

Thank you very much for your help!

[tresesco · Accepted solution]

Try expression like:

Count({<ID = {"=Sum([Outcome/Income]) =0"}>} Distinct ID)

[Vengatesh]

Try this, If(Aggr(Sum(Value),ID)=0,Aggr(Sum(Value),ID)) as calculated dimension and Count(Distinct ID) as Measure.