cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

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

 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
• 100
• ConversionId
• 2

Current Measure:

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

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

Appreciate any assistance with this!

1 ACCEPTED SOLUTION
Super User

Hi @Kcloud ,

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:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

2 REPLIES 2
Super User

Hi @Kcloud ,

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:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

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.