cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kcloud
Helper I
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    

 

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!

1 ACCEPTED SOLUTION
MFelix
Super User
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:

MFelix_0-1664317844905.pngMFelix_1-1664317940356.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
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:

MFelix_0-1664317844905.pngMFelix_1-1664317940356.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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! 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors