Measure to sum the count of Distinct Id's from separate tables, with conditional exclusion

[Anonymous]

Hi All,

Trying to make a measure for KPI tracking where I'm counting each unique creation for a User, i.e.

• If the User created the Opportunity/Lead and then the resulting Conversion, I only want to count that as 1.
• If the User created the Conversion from someone elses Opportunity/Lead, I want it to count as 1 for both Users.

This seems like it should be simple but it's just not working the way I'd expect.

My three tables are as below:

Table A: 'Conversion'

ConversionId		CreatedById		LeadId		OpportunityId
1		10		100
2		10				1000
3		20		200		2000
4		20		200		2000
5		10		300		2000
6		20

Table B: 'Lead'

LeadId		CreatedById		OpportunityId
100		10
200		20		2000
300		10		2000

Table C: 'Opportunity'

OpportunityId		CreatedById		ConversionId		LeadId
1000		20		2
2000		10				200
2000		10				300
3000		10
4000		20

I want to count the unique Id's within each Table to link back for each Staff member (CreatedById) so that the end result for Staff (CreatedById) 10 & 20 would look like this:

Staff Id		Staff Name		Unique Creations
10		Allison A		4
20		Brendan B		4

Reasoning: 

• Allison A (Id 10) Created 3 Opportunities, 2 Leads and 3 Conversions.
  • Of the 3 Conversions, One was from their Created Lead, One was from their Created Opportunity
  • Of the 2 Leads, One was from their Created Opportunity
• Therefore their Unique Creations are made up from:
  • OpportunityId's
    • 2000
    • 3000
  • LeadId
    • 100
  • ConversionId
    • 2

Current Measure:

    CALCULATE(   
        CALCULATE(
                    DISTINCTCOUNT('Conversion'[ConversionId])
                ,   USERELATIONSHIP('Conversion'[CreatedById], 'User'[Id])
                ,   'Conversion'[OpportunityId] = BLANK()
                ,   'Conversion'[LeadId] = BLANK()
            )
        +
            CALCULATE(
                DISTINCTCOUNT('Opportunity'[Id])
            ,   USERELATIONSHIP('Opportunity'[CreatedById], 'User'[Id])
            )
        +
            CALCULATE(
                DISTINCTCOUNT('Lead'[Id])
            ,   USERELATIONSHIP('Lead'[CreatedById], 'User'[Id])
            )
        )

This is the closest I've managed, however in my tables above, this would exclude ConversionId 6

Appreciate any assistance with this!

[MFelix · Accepted solution]

Hi @Anonymous ,

Do you have any other relationships between your tables? How is your model setup?

Believe this is a question about the relationships your model is using, making use of your metric has you can see below with a model with the relationship in the image below everything works properly:

[Anonymous]

Hi @MFelix ,

I believe you're right. I kept looking at the measure but didn't check the relations.

The model is quite extensive with approximately 30 tables, and after reviewing the four involved it does appear that the cardinality on one of them is incorrect. 

Thank you for your help!