Count of Distinct Values In One Column When Condition In Another Column Is Met

[#1]

Hi,

I have a table that looks like:

I have "Product Id" in a straight table. For each "Product Id" row, I want to display a column that displays a count according to the following logic: Distinct "Partner ID" values for each "Product Id" where "Status Type" = "CON." I think I know how to just do a simple distinct count, but I'm not sure about the proper syntax for the AND Status Type = CON condition.

If you could be detailed about where exactly I need to put this code in the straight table properties, I would really appreciate it (I'm still pretty new to this). Thanks!

-Kenny

[marcus_sommer]

You could do it with set analysis within the expression like:

count({< [Status Type] = {'CON'}> } distinct [Partner ID])

- Marcus

[sunny_talwar · Accepted solution]

Try this as your expression:

Count(DISTINCT {<[Status Type] = {"CON"}>} [Partner ID])

and dimension Product Id

[#4]

Thanks Marc/Sunny for the really quick response. I'm sure one of these works perfectly. I'll test on Mon. and be sure to mark the correct answer. Really appreciate the help!

[sunny_talwar]

No problem Kenny

We both actually gave the same solution, just the place of DISTINCT is different.

Best,

Sunny

[abhi1693r]

Hello,

I had a similar issue. So I have two columns, PART AND CUBES, with one to many relationship.(one PART can have many CUBES or a PART can have no CUBE).

Scenario: If I select 3 PARTS which have 2 CUBES, wherein 2 PARTS have 2 CUBES and 1 PART has no cube. I want a count of all the CUBES plus any PART which has no CUBES.

So the count should be 2+1=3.

How can I implement this?

@sunny_talwar@marcus_sommer 

[marcus_sommer]

I'm not sure that I understand what your difficulties are but you could also count over multiple fields maybe with something like this:

count(distinct PART&CUBES)

- Marcus