topic Re: Count distinct with IF/Filter formula on measure in App Development

Count distinct with IF/Filter formula on measure

Tienemientje — Mon, 06 Feb 2023 14:07:53 GMT

Hi,
I'm quite new to QS and I have a question on a calculation I want to make.

I want to count the number of distinct customers that got a value of a certain category.

Example data in my pivot:

Person	Fee	Other
An	100	10
An	350	0
Ben	0	100

So I want to count:

Fee	Other
1 (person)	2

Can you help me identify a good formula for this case?

PS: I found a couple of similar cases, but either they were to complicated or the provided answer did not work in my case.

Re: Count distinct with IF/Filter formula on measure

E_Røse — Mon, 06 Feb 2023 16:41:31 GMT

@Tienemientje , What did you try, and how was it not working as you expected? With a 'certain category', do you mean within a given interval or a certain value?

Re: Count distinct with IF/Filter formula on measure

Tienemientje — Mon, 06 Feb 2023 18:46:26 GMT

With the category, I mean like in my example "fee" and "other".

I tried a number of variations on the following expression

Count (distinct [Name] and [Fees]>0)

It either gives an error, like if Y try to add the IF statement it says that it needs more parts. (which I can understand, similar to excel, but I found examples online where they did not seem to add the "if not" part). Or it gives a zero or just some high number (either not the distinct values or not only those names for which the fee is above 0)

I now received something from a colleague, but I need to check further if it gives exactly the values I want

Count(distinct{<[name]={"=sum([fee]>0"}>}[name])

Re: Count distinct with IF/Filter formula on measure

E_Røse — Mon, 06 Feb 2023 19:48:54 GMT

Will

count({<Fee={">0"}>}distinct Person) and

count({<Other={">0"}>}distinct Person)

give you the numbers that you want, or do you somehow need to consider the sums per person? (asking because of your collegue's suggestion)