Count with conditionals

[antupe]

Hello,

I have product ID, Model and Broken as imported variables. The same product ID may appear more than once in the table (for other reasons) and this must not be changed.

Any recommendations on how I could do a count of each model where broken=0 and where if the ID is repeated, it is only counted once?

I was using: 

=count(if((Broken='No'),[Model]))   

but I am not sure how to implement the distinct(ProductID) in the expression.

Thank you in advance

[Or]

Count(distinct {< Broken = {'No'} >} Model) should work. Using set analysis rather than if() is recommended when possible, generally speaking.

[antupe]

I would like to apply the distinct to the Product ID, I think your suggested expression does not include than (distinct is apparently acting on model)

[Or]

Then you should just count Product ID instead of Model. Since it's a count distinct, there won't be repetitions.

[antupe]

Sorry I think I may not have explained myself very clearly. 

This is an example of the information I have:

Product ID	Model	Broken
001	A	No
010	B	No
002	A	No
004	A	No
010	B	No
006	A	Yes
004	A	No
012	B	No

As you can see there are repeated IDs that must remain so because of other variables not mentioned here.

I would like to have the total number of items that are of Model A and of Model B (this is a simplification, there are more), where the state of broken is 'No'. As some IDs are repeated I would like to count these only once in the model count.

So for this example my expected result would be 

Model

A=3 (corresponding to 001, 002, 004, not 006 because the state for broken is 'Yes')

B=2 (corresponding to 010, 012)

Sorry for the unclear initial explanation

[Or]

I actually think it was pretty clear in the first place, but I'm not sure how this differs from the suggested solution. Model is your dimension, and the expression is what I suggested above (replacing Product ID for Model in the count distinct).