Dear Community,
this is a problem I'm trying to solve in the presentation layer without resorting to script changes. In short, I have two columns of names in my data, and I want to count the number of distinct names in the union of the two columns, per dimension value.
My data describes the relationships between marketing relationship managers globally - who is passing leads & deals to whom transnationally. Its columns include
- DealId (PK)
- CustomerIndustrySegment (Utilities, Resources, etc.)
- FromRelationshipManager
- ToRelationshipManager
- PotentialDealSize
I am needing to count all the distinct Relationships Managers (RM) for each Industry Segment, irrespective of whether the RM is a FromRelationshipManager or a ToRelationshipManager. Note that (a) RMs are frequently in both columns(b) An RM can be in both columns for the same deal, if the deal is both discovered and handled within the one country.
Example:
| DealId | CustomerIndustrySegment | FromRelationshipManager | ToRelationshipManager |
| 1 | Utilities | Anne | Anne |
| 2 | Utilities | Bob | Charlie |
| 3 | Utilities | Dorothy | Dorothy |
| 4 | Utilities | Dorothy | Egbert |
| 5 | Utilities | Felicity | Egbert |
| 6 | Utilities | Felicity | George |
| 7 | Utilities | George | George |
| 8 | Resources | Helen | George |
Given the above, I'm wanting to generate
- Utilities - 7 (being all RMs from A to G)
- Resources - 2 (being George & Helen)
So, in pseudocode, for each CustomerIndustrySegment, I am wanting count(union of (distinct FromRelationshipManagers and distinct ToRelationshipsManagers for the given CustomerIndustrySegment)).
I've considered expressions that attempt to mix'n'match various counts, like (in pseudocode):
- count(distinct FromRelationshipsManager) + count(distinct ToRelationshipsManager) - count(distinct FromRelationshipsManager in records for which FromRelationshipsManager<>ToRelationshipsManager)
(this gives 8 and 1 for Utilities and Resources respectively)
but cannot find anything that gives the right result.
The only solution I've been able to come-up with is to change my data, unpivoting the Relationship Manager columns in the script so that we have
| DealId | CustomerIndustrySegment | Direction | RelationshipManager |
| 1 | Utilities | From | Anne |
| 1 | Utilities | To | Anne |
| 2 | Utilities | From | Bob |
| 2 | Utilities | To | Charlie |
| 3 | Utilities | From | Dorothy |
| 4 | Utilities | To | Dorothy |
| : | : | : | : |
and then we could use count(distinct RelationshipManager). But I'd rather not change the underlying data, if I can help it.
Does anyone know how to achieve what I'm trying to do?
