Count multiple values based on distinct values

[#1]

Hi Team,

I am trying to write an expression which would list down all customers who are not following the below rule.

Rule to be followed is: One Project ID should only have a single Customer.

Eg:

Project ID	Customer Name
112	SERVICES PRIVATE LIMITED
112	GlobalSoft Pvt ltd
345	Tech Pvt ltd
345	UI PRIVATE LIMITED

=aggr(if(Count(DISTINCT([Project ID]))=1,if(count(distinct([Customer Name]))>1,[ Customer Name])),[Customer Name])

I tried set analysis with aggregation function couldn't figure out how to get the above solution

[Anil_Babu_Samineni]

What was the logic behind on this, I am not fully understand your concern. I am assuming this?

I would highly recommend to you, Don't create 2 Threads for single issue

If(Count(DISTINCT [Project ID]) = 1 and Count(DISTINCT [Customer Name]) >1, [Customer Name])

OR

Aggr(If(Count(DISTINCT [Project ID]) = 1 and Count(DISTINCT [Customer Name]) >1, [Customer Name]), [Customer Name],[Project ID])

[swuehl]

Maybe create a straight table with project id and customer name as your two dimensions and as expression

=Count(TOTAL<[Project ID]> DISTINCT [Customer Name]) = 1

Enable 'suppress zero values' on presentation tab (and you can hide the expression column if you want, too)

This should only show the combinations of projects and customers where your condition is fulfilled.

[sunny_talwar · Accepted solution]

May be this in a straight table:

Dimension

Customer Name

Expression

Avg({<[Project ID] = {"=Count(DISTINCT [Customer Name]) > 1"}>}1)

or text box object like this

Concat(DISTINCT {<[Project ID] = {"=Count(DISTINCT [Customer Name]) > 1"}>} [Customer Name]), Chr(10))

[MarcoWedel]

or using calculated listbox:

hope this helps

regards

Marco

[#6]

Thanks Marco for the file. It helped me to analyze the data a from a different view.

[MarcoWedel]

you're welcome

glad it helped

regards

Marco