distinct count-exclude nulls or blanks or zero

[surajap123]

Hi Experts,

I have the below dataset and I want to show distinct count of Products(but exclude products where the exp field has null, blank or 0 values)

Data-->

Category Product exp sales

cat1 A 34 100

cat1 A 20

cat1 B 100

cat1 C 10 50

cat1 D 0 50

In straight table i am using Category as dimension and below expression

Count({<exp =- {"=IsNull(exp)","=len(exp)",0}>} DISTINCT Product)  //not working

Please help

[Anil_Babu_Samineni]

This?

Count({<exp -= {"=IsNull(exp)","=len(exp)=0"}>} DISTINCT Product)  //not working

[surajap123]

The expression is returning 3. As you can see only 2 distinct products satisfies the condition.

[prat1507]

Maybe this

Count({<exp -= {"=IsNull(exp)","=len(exp)=0","0"}>} DISTINCT Product)  //not working

[sunny_talwar · Accepted solution]

How about this

Count({<Product = {"=Sum(exp) = 0"}>} DISTINCT Product)

[Anil_Babu_Samineni]

What are you getting when you use these?

Count({<exp -= {'-','0'}>} sales)

Count({<exp = {"=Sum(exp) > 0"}>} sales)

[surajap123]

Thanks sunny